Wednesday, March 7, 2012

help turning this Sql statement into a stored procedure

Hello, I need a little help turning this:
SELECT RequestNum FROM Tickets WHERE ReceiptDate>='" & FromDate & "' AND ReceiptDate<='" & ToDate & "'"
into a sproc because of the two different values (FromDate and ToDate) for the ReceiptDate field in the database.
I have this so far (problem areas are ??):
Dim AuditConnection As New SqlConnection(ConnString)
Dim AuditCommand As New SqlCommand("CreateAudit", AuditConnection)
AuditCommand.CommandType = CommandType.StoredProcedure
AuditCommand.Parameters.Add(New SqlParameter("@.??", SqlDbType.NVarChar)).Value = FromDate
AuditCommand.Parameters.Add(New SqlParameter("@.??", SqlDbType.NVarChar)).Value = ToDate
AuditConnection.Open()
Dim AuditResult As SqlDataReader = AuditCommand.ExecuteReader()
AuditGrid.DataSource = AuditResult
AuditGrid.DataBind()
AuditConnection.Close()
and:
CREATE PROCEDURE CreateAudit
??
??

AS
SELECT
RequestNum
FROM
Tickets
WHERE
??
AND
??
GO
I know I'm an idiot and this should be something simple. Arrrgh. Any help is appreciated immensely!!! :)

Dim AuditConnection As New SqlConnection(ConnString)
Dim AuditCommand As New SqlCommand("CreateAudit", AuditConnection)
AuditCommand.CommandType = CommandType.StoredProcedure
AuditCommand.Parameters.Add(New SqlParameter("@.FromDate", SqlDbType.DateTime)).Value = FromDate
AuditCommand.Parameters.Add(New SqlParameter("@.ToDate", SqlDbType.DateTime)).Value = ToDate
AuditConnection.Open()
Dim AuditResult As SqlDataReader = AuditCommand.ExecuteReader()
AuditGrid.DataSource = AuditResult
AuditGrid.DataBind()
AuditConnection.Close()
and:
CREATE PROCEDURE CreateAudit
@.FromDate datetime,
@.ToDate datetime

AS
SELECT
RequestNum
FROM
Tickets
WHERE ReceiptDate>=@.FromDate AND ReceiptDate<=@.ToDate

Presumes ReceiptDate, FromDate and ToDate are datetime types and not nvarchar|||aaaaahhhh, I see...THANK YOU!!!

No comments:

Post a Comment