Dynamic SQL Stored Procedure taking date as variable

  • Hi I have a stored procedure which takes a tablename and a date as variables and runs a select on the table with those criteria.

    Unfortunately it's not working for me. Any ideas appreciated.

    CREATE PROCEDURE UK_busy_report
       @TableName varchar(255) 
    , @reporteddate varchar(40)ASBEGIN
    DECLARE @SQLStatement varchar(255)             
    DECLARE @date datetime
    SELECT @reporteddate=CONVERT(datetime, @date, 103)IF @@ERROR <> 0 
    BEGIN  Print 'ERROR'
    END
    ELSE    
    SELECT @SQLStatement = 'SELECT vendor, reporteddate, 
    count(vendor) FROM ' 
    + @TableName + 'WHERE reporteddate =
     ' + ''''+ convert (nvarchar(14), 
    @reporteddate, 101) + ''''    
    EXEC(@SQLStatement)ENDGO

    .logic.

  • Firstly I don't know why you convert your date parameter to datetime then back

    to nvarchar. Why not just use the passed value as it is?

    In the statement where you convert the date parameter to datetime you

    had the variables in the wrong spots. You were effectively setting the @reportdate

    variable to NULL. Also, there was a space missing before the WHERE keyword and

    you can't have an aggregate function without a GROUP BY.

    CREATE PROCEDURE UK_busy_report

        @TableName varchar(255)

      , @reporteddate varchar(40)

    AS

    BEGIN
     DECLARE @SQLStatement varchar(255)
     DECLARE @date datetime
     SELECT @date = CONVERT(datetime, @reporteddate, 103)
     IF @@ERROR <> 0
     BEGIN
      Print 'ERROR'
     END
     ELSE
      SELECT @SQLStatement = 'vendor, reporteddate, count(vendor) FROM '
       + @TableName + ' WHERE reporteddate = '''
       + convert (nvarchar(14), @date, 106) + ''''
       + ' GROUP BY vendor, reporteddate'
        EXEC(@SQLStatement)
    END
    GO

     

    PS: Can you edit you post so it's not so wide

     

    --------------------
    Colt 45 - the original point and click interface

  • Hi Phill, thanks for the reply.

    When I exec that I've tried using the following format:

    exec UK_busy_report EU_master_week6, '02/02/04'

    I was searching the EU_master_week6 table for any dates matching the 02 February 2004?

    I've also tried playing around with the convert function and changing the number from 101 through to 120

    My database stored the date in a smalldatetime field using yyyy-dd-mm hh:mm:ss format.

    Every time I get the same error: Server: Msg 241, Level 16, State 1, Procedure UK_busy_report, Line 7

    Syntax error converting datetime from character string.

    thanks for the help,

    .logic.

     

     

     

     

  • Style is only used when converting to character.

    Use

    SELECT @date = CONVERT(datetime, @reporteddate)

    Also the test of @@ERROR will not be used as failure in date conversion will cause Level 16 error will will stop the procedure.

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Converting a string date to datetime format always yields a datetime field where the time portion is '00:00:00.000'. If your ReportedDate field on the database contains full date/time values (i.e. 02/08/2004 17:31:21.123) and you are comparing to your converted date which, based on the above statement contains 'mm/dd/yyyy 00:00:00.000' you will never get an "equals" state. 

    If your data contains full datetime vales (i.e. they were set using Getdate() or some other real-time function) then you will need to trim off the time portion to get a valid comparison using strings, or else compute two datetime values, ReportedDate and ReportedDate+1 and then use:

    Where ReportedDate>= ' + ''''+convert(nvarchar,@date) + ''' AND ReportedDate <= ' + ''''+convert(nvarchar,@dateadd(Day,+1,@date) + ''''

    This should give you something like ">= 02/04/2004 00:00:00.000 AND <= 02/05/2004 00:00:00.000" which will give you all the records with '02/04/2004' as a date.

    It's unfortunate that SQL doesn't have Date and Time datatypes with the corresponding functions, but it is what it is.

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply