August 6, 2004 at 1:25 pm
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.
August 6, 2004 at 1:33 pm
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
August 6, 2004 at 1:53 pm
Thanks.It worked really well.
August 8, 2004 at 11:39 pm
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
August 9, 2004 at 9:26 am
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