Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: stored procedure with Dynamic sql

    CREATE PROCEDURE ggg_test_sp
    @start_date datetime,@end_Date datetime
    AS

    SET NOCOUNT ON
    DECLARE @sqlstmt varchar(1000)

    SELECT @sqlstmt='SELECT * FROM ggg_emp WHERE date_join BETWEEN ' +CONVERT(varchar(10),@start_date-1,101) + ' AND ' +CONVERT(varchar(10),@end_Date+1,101)

    SELECT @sqlstmt
    EXEC (@sqlstmt)

    GO


    I want to apply date filter in the above sp with dynamic sql stmt. When i execute the above procedure with date ranges( @start_date=07/06/2004 AND @end_Date= 08/06/2004)i am not getting any result because my @sqlstmt variable has the select stamet

    SELECT * FROM ggg_emp WHERE date_join BETWEEN 07/06/2004 AND 08/06/2004

    BUT it should have the sqlstmt as

    SELECT * FROM ggg_emp WHERE date_join BETWEEN '07/06/2004' AND '08/06/2004' to produce the required result

    I know that for the above SP we dont need any dynamic sql but this is just an example.

    So anyone can help me on this issue.

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think you just have to double up on the quotes. Like escaping special characters in shell scripts.

    '' = 1 quote
    ''' = 1 quote and end string
    '''' = 2 single quotes

    Or something like that.

    So you would have:
    Code:
    SELECT @sqlstmt='SELECT * FROM ggg_emp WHERE date_join BETWEEN ''' +CONVERT(varchar(10),@start_date-1,101) + ''' AND ''' +CONVERT(varchar(10),@end_Date+1,101) + ''''
    But it will take a bit of experimenting to fine tune it. good luck.

Posting Permissions

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