Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: Stored Proc Date Range not producing RS

    I am going crazy. I have a typical date field in tbl_Feedback(smallDateTime) that is storing just the date portion. I have a stored proc that I want to dynamically retrieve records within a certain date range (typical!). I am using ASP/ADO, etc. When I run the following Stored Proc in SQL Analyzer, it works (if I replace the variable "@sDate" with '10/01/2004' --There ARE records within this date range.) Further more, If I run a simple Query on the Feedback table and do the same, it produces results, but when I call the stored Proc from the web page, I get 0 results when attempting to PASS date values dynamically. I tried to pass them as actual date types, still 0. I'm now attempting to pass it as a 10 length string ("10/01/2004") and converting it to a date, still 0. What am I MISSING???

    -- Procedure

    CREATE PROCEDURE sp_009_SiteLead
    @sDate nVarChar(10)

    AS

    --DECLARE @CurDate datetime
    DECLARE @StartMonth int
    DECLARE @StartYear int
    DECLARE @StartDay int
    DECLARE @StartDate varchar(10)


    SET @StartMonth = DATEPART( month, Convert ( datetime, @sDate ))
    SET @StartYear = DATEPART( year, Convert ( datetime, @sDate ))
    SET @StartDay = DATEPART( day, Convert ( datetime, @sDate ))
    SET @startDate = CONVERT( varchar(2), @StartMonth ) + "/" + CONVERT( varchar(2), @StartDay ) + "/" + CONVERT( varchar(4), @StartYear )
    Print @startDate

    SELECT *
    FROM tbl_Feedback
    WHERE tbl_Feedback.DateIn >= @startDate
    GO

    --- ADO Web page

    ' Stored Procedure Name: sp_009_SiteLead
    ' Site Lead Default Query

    set cmd=server.CreateObject("ADODB.command")
    set psDate = cmd.CreateParameter("sDate", adDBTimeStamp, adParamInput)
    set cmd.ActiveConnection=DBConnection
    cmd.CommandText="sp_009_SiteLead"
    cmd.Parameters.Append peDate
    cmd.Parameters.Append psdate
    psDate.Value = "10/01/2004"
    Set rsObj = CreateObject("ADODB.Recordset")
    rsObj.ActiveConnection = DBConnection
    rsObj.CursorLocation = adUseClient
    rsObj.PageSize = 20
    rsObj.Open cmd

    Again, If I replace the @startDate variable in the stored proc with a date surrounded in single quotes, I get the expected results, but it just does not understand my date if I pass it.
    Can anyone help?

    Thanks,

    Chad
    Last edited by DEFENDER; 10-11-04 at 18:59. Reason: Title mis-spelling...

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If DateIn is smalldatetime why don't you define @sDate as smalldatetime?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Oct 2004
    Posts
    4

    Stored Proc not returning RS

    I tried "datetime", "smalldatetime" formats 101, 102, 112, etc.

    My SP looks like this currently:
    CREATE PROCEDURE sp_009_SiteLead
    @sDate nVarChar(10),
    @eDate nVarChar(10)

    AS

    Declare @startDate smalldatetime
    Set @startDate = CONVERT(smalldatetime,CONVERT(nvarchar,@sDate,101) )
    Declare @endDate smalldatetime
    Set @endDate = CONVERT(smalldatetime,CONVERT(nvarchar,@eDate,101) )

    SELECT bla bla bla...
    WHERE
    ( ( tbl_Feedback.DateIn >= @startDate ) AND ( tbl_Feedback.DateIn <= @endDate ) )
    OR
    ( ( tbl_Feedback.DateIn >= @startDate ) AND ( tbl_Feedback.DateIn <= @endDate ) )

    GROUP BY bla bla bla...
    GO

    I've been working on this for 2 frigging days and I am going insane!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    If I put, "Set @startDate = '07/01/2004' after the declarations, then voila! I get results. WHY FOR THE LOVE OF GOD DOES IT NOT KNOW THAT THE DATE TEXT I AM PASSING IS A FREAKIN' DATE? It works in SQL analyzer AND if I pass the Select statement as text and open a rs the olde fashioned way. I love SPs and I have used them for years. I'm not really this stupid!!! Really!

  4. #4
    Join Date
    Oct 2004
    Posts
    4

    Ignore the extra statement after 'OR'

    The OR clause was required because the query is actually more complex. I shrunk it down for purposes of posting.

  5. #5
    Join Date
    Oct 2004
    Posts
    4

    I Figured It Out!!!!!!!

    When you pass parameters to a stored proc from ADO, all the vars MUST be passed EXACTLY in the order in which the stored proc expects them -- EVEN THOUGH yuo define the values by var name.

    I figured it out becasue I reversed the start and end date in the query and it worked! I was baffled at first until I realized that I also specified the END date BEFORE the START date. VOILA!

    WHEW! I will NEVER make that mistake again!!! Feels good. Time for a soda!

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I WAS NOT about to say that, but that's a good point. Another guarantee is to use named parameters, as they are named in the procedure:

    mSQL = "exec sp_myproc @param1 = <value>, @param2 = <value>..." etc.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •