stored procedure with Dynamic sql

Question

stored procedure with Dynamic sql

sahana

SSCrazy

Points: 2530
More actions
August 6, 2004 at 1:25 pm

#181476

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.

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

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

chrisrock Old Hand Points: 369 More actions · Answer 1

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

_______________
Convert DTS to SSIS | Database Documentation | SSIS Performance | SSIS Monitoring

sahana SSCrazy Points: 2530 More actions · Answer 2

sahana

SSCrazy

Points: 2530

August 6, 2004 at 1:53 pm

#518078

Thanks.It worked really well.

~nano Right there with Babe Points: 794 More actions · Answer 3

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

Sam Gordon SSC-Addicted Points: 411 More actions · Answer 4

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