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

    Unanswered: MS SQL Dynamic stored procedure using a datetime variable

    Hi I'm new to MS SQL and trying to write a very small dynamic stored procedure which is giving me a headache.

    What I have is:

    Code:
    CREATE PROCEDURE busy_report
    
     @TableName varchar(255),
     @reporteddate datetime=NULL
    
    AS  
     if @reporteddate is null
              select @reporteddate = CURRENT_TIMESTAMP
    
        -- Create a variable @SQLStatement
        DECLARE @SQLStatement varchar(255)
    SET DATEFORMAT dmy   
    
        -- Enter the dynamic SQL statement into the
        -- variable @SQLStatement
        SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
    @TableName + "WHERE reporteddate = ' "
    + @reporteddate + " '"
    
    
        -- Execute the SQL statement
        EXEC(@SQLStatement)
    GO
    The error I keep getting is:

    Server: Msg 8114, Level 16, State 4, Procedure busy_report, Line 0
    Error converting data type varchar to datetime.


    Any ideas appreciated.

    (Edit

    I've also tried it this way:

    Code:
    CREATE PROCEDURE UK_busy_report
    
     @TableName varchar(255),
     @reporteddate datetime=NULL
    
    AS  
     
        -- Create a variable @SQLStatement
        DECLARE @SQLStatement varchar(255)
    SELECT @reporteddate=CONVERT(datetime, @reporteddate)
    IF @@ERROR <> 0 BEGIN
    
    /* Do some error processing */
    
    PRINT 'Error Occured' END
      
    ELSE
        -- Enter the dynamic SQL statement into the
        -- variable @SQLStatement
        SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
    @TableName + "WHERE reporteddate = ' "
    + @reporteddate + " '"
    
    
        -- Execute the SQL statement
        EXEC(@SQLStatement)
    GO
    Which gives me the same error!

    .logic.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ahhh...the smell of Oracle.....

    Code:
    CREATE PROCEDURE busy_report
     	  @TableName varchar(255)
     	, @reporteddate datetime=NULL
    AS
    BEGIN
    	DECLARE @SQLStatement varchar(255)
    
    	IF @reporteddate IS NULL
              SELECT @reporteddate = GetDate()
    
        SELECT @SQLStatement = 'SELECT vendor, reporteddate, count(vendor) FROM ' 
    	+ @TableName + 'WHERE reporteddate = ' 
    	+ ''''
    	+ @reporteddate 
    	+ ''''
    
        EXEC(@SQLStatement)
    END
    GO

    It's been a while

    If you want specifc date formats look up CONVERT in Books Online (BOL)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It gives you the same error because it occurs on the last concatenation of @SQLStatement.

    "WHERE reporteddate = '" + convert(char(10), @reporteddate, 101) + "'"

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by rdjabarov
    It gives you the same error because it occurs on the last concatenation of @SQLStatement.

    "WHERE reporteddate = '" + convert(char(10), @reporteddate, 101) + "'"
    Yeah...you're definetley going to need to worry about conversion...

    What's the column reporteddate defined as?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Posts
    4
    Originally posted by Brett Kaiser
    Yeah...you're definetley going to need to worry about conversion...

    What's the column reporteddate defined as?

    Hi, the column is a smalldatetime type.

    With your solution Brett I' getting a Server: Msg 295, Level 16, State 3, Procedure UK_busy_report, Line 11
    Syntax error converting character string to smalldatetime data type.


    Using:

    Code:
    CREATE PROCEDURE UK_busy_report
     	  @TableName varchar(255)
     	, @reporteddate smalldatetime=NULL
    AS
    BEGIN
    	DECLARE @SQLStatement varchar(255)
    
    	IF @reporteddate IS NULL
              SELECT @reporteddate = GetDate()
    
        SELECT @SQLStatement = 'SELECT vendor, reporteddate, count(vendor) FROM ' 
    	+ @TableName + 'WHERE reporteddate = ' 
    	+ ''''
    	+ @reporteddate 
    	+ ''''
    
        EXEC(@SQLStatement)
    END
    GO
    I've also tried to convert it to nvarchar as follows:

    Code:
    CREATE PROCEDURE UK_busy_report
     	  @TableName varchar(255)
     	, @reporteddate smalldatetime=NULL
    AS
    BEGIN
    	DECLARE @SQLStatement varchar(255)
    
    	IF @reporteddate IS NULL
              SELECT @reporteddate = GetDate()
    
        SELECT @SQLStatement = 'SELECT vendor, reporteddate, count(vendor) FROM ' 
    	+ @TableName + 'WHERE reporteddate = ' 
    	+ ''''
    	+ convert (nvarchar(14), @reporteddate, 101) 
    	+ ''''
    
        EXEC(@SQLStatement)
    END
    GO
    And I get: Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near '='.


    I think this conversion is the way to go but not sure of exact syntax. I'll keep checking through BOL and if anyone has any more ideas they'd be greatly appreciated

    .logic.

  6. #6
    Join Date
    Feb 2004
    Posts
    4
    I've again altered it to:

    Code:
    CREATE PROCEDURE UK_busy_report
     	  @TableName varchar(255)
     	, @reporteddate varchar(40)
    AS
    BEGIN
    	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)
    END
    GO
    I'm running it with: exec UK_busy_report EU_master_week6, '02/02/04';

    And it seems to be running fine but it doesn't return any info, even though I know that date exists in the table.

Posting Permissions

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