DateTime Convert Problem in Dynamic Stored Proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

DateTime Convert Problem in Dynamic Stored Proc

Author

Topic

twinkle8
Starting Member

3 Posts

Posted - 2003-11-18 : 07:40:13

Hi guys

I am trying to build a dynamic select statement in a stored procedure which takes as a variable a datetime object I am passing in.

I have tried two ways of doing this:

Declaring the DateTime objects as varchar and creating the SELECT statement as

DECLARE @SQLStatement varchar(1000)
set @SQLStatement =
'SELECT feedback_question_type_id, feedback_email_id, feedback_Topic_id '
+ ' FROM table_name'
+ ' WHERE (feedback_Created_dt BETWEEN '
+@from_dt +
' AND '
+@to_dt +')'

This parses and runs, but I do not get the results I would expect.

I have also tried using the Dates as DateTime objects and converting them,

'SELECT feedback_question_type_id, feedback_email_id, feedback_Topic_id
FROM feedback
WHERE (feedback_Created_dt BETWEEN CONVERT(DATETIME,'''+@from_dt+''', 102) AND CONVERT(DATETIME, '''+@to_dt+''', 102))'

but I am getting the 8114 message when I try to run....I suspect the problem is to do with including DateTime variable in the Select statement which is a VarChar. Any Advice?

Cheers

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-11-18 : 07:46:42

Why are you doing it this way?

CREATE PROCEDURE mySproc @from_dt datetime, @to_dt datetime AS
SELET feedback_question_type_id, feedback_email_id, feedback_topid_id
FROM table_name
WHERE feedback_Created_dt BETWEEN @from_dt AND @to_dt

No need for dynamic SQL at all.

twinkle8
Starting Member

3 Posts

Posted - 2003-11-18 : 07:49:31

Thanks for reply - however I have to build up SQL Statement dependent on variables that I pass in such as

if (@topic_id <> 0)
BEGIN
SET @SQLStatement = @SQLStatement + ' AND feedback_Topic_id = '+ @topic_id
END

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-18 : 08:05:07

You can use dynamic SQL to accomplish this but it should be a last resort in terms of performance. What is the logic you are evaluating for this proc. Is it just the addition of @topic_id condition or more? I ask because the above (@topic_id condition) can be expressed in terms of AND/OR logic or CASE logic.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-11-18 : 08:14:47

You still don't need dynamic SQL:

CREATE PROCEDURE mySproc @from_dt datetime, @to_dt datetime, @topic_id int=Null AS
SELET feedback_question_type_id, feedback_email_id, feedback_topid_id
FROM table_name
WHERE feedback_Created_dt BETWEEN @from_dt AND @to_dt
AND (@topic_ID IS NULL OR topic_id=@topic_id)

You can have as many variables as you like or need, if you follow the structure highlighted in red. Allow any conditional variable to default to null in the procedure, and include a WHERE condition structured like the one listed for @topic_id.

twinkle8
Starting Member

3 Posts

Posted - 2003-11-18 : 08:24:57

Thank you very much - I didn't realise it could be done like that.

Thanks again

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-18 : 16:19:44

nice one, Rob !!

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-11-18 : 20:00:25

Yeah, I just read an article someone posted on using that technique instead of IsNull() and CASE expressions.