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.

  • 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)

    should be

    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) + ''''

  • Thanks.It worked really well.

  • Why the dynamic SQL???

    Why not just use the Statement as is? Why build it dynamically in this case?

    SELECT * FROM ggg_emp 

    WHERE CONVERT(varchar(10),date_join,101)

    BETWEEN CONVERT(varchar(10),@start_date-1,101)

    AND CONVERT(varchar(10),@end_Date+1,101)

    just a thought - but I am always told that dynamic SQL should be avoioded if possible to push effectiveness

    Greetings from Germany

    ~nano

  • Nano,

    You may be right. But strangely enough I have been building sp in my search engine and conventional statement didn't worked right. I rewrote statement in dynamic sql and it worked nice.

    Sam

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

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