Datetime and sp_executesql Issue

Question

Datetime and sp_executesql Issue

RovanSQL

SSC Enthusiast

Points: 103
More actions
June 13, 2012 at 10:24 am

#258059

Here's part of code i've been trying to run in a procedure, cursor is already defined:
DECLARE @form_dates CURSOR
SET @form_dates= CURSOR FOR
SELECT courseid, registrationdate, completeddate
FROM tblname
WHERE UserID = @tempID
ORDER BY courseID;
OPEN @form_dates
FETCH NEXT FROM @form_dates INTO @cid, @regdate, @compdate
/* PRINT @regdate
PRINT @compdate This is correctly having values in datetime. eg: Mar 28 2012 6:03PM
PRINT @cid */
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N'update tblname set cs' + CAST (@cid AS VARCHAR(10)) + '_begin = @rstring , cs' + CAST (@cid AS VARCHAR(10))+ '_end = @cstring where UserID = '+ CAST (@tempID AS VARCHAR(10))
DECLARE @ParmDefinition nvarchar(500) SET @ParmDefinition = N'@rstring datetime, @cstring datetime'
EXECUTE sp_ExecuteSQL @SQL, @ParmDefinition, @rstring = @regdate, @cstring = @compdate
I get this output
update tblname set cs32_begin = @rstring , cs32_end = @cstring where UserID = 419
Here, litrals @cstring and @rstring do not have their values at output.
Please help. Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

RovanSQL (6/13/2012)
Here's part of code i've been trying to run in a procedure, cursor is already defined:
OPEN @form_dates
FETCH NEXT FROM @form_dates INTO @cid, @regdate, @compdate
/* PRINT @regdate
PRINT @compdate This is correctly having values in datetime. eg: Mar 28 2012 6:03PM
PRINT @courseno */
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N'update tblname set cs' + CAST (@cid AS VARCHAR(10)) + '_begin = @rstring , cs' + CAST (@cid AS VARCHAR(10))+ '_end = @cstring where UserID = '+ CAST (@tempID AS VARCHAR(10))
DECLARE @ParmDefinition nvarchar(500) SET @ParmDefinition = N'@rstring datetime, @cstring datetime'
EXECUTE sp_ExecuteSQL @SQL, @ParmDefinition, @rstring = @regdate, @cstring = @compdate
I get this output
update tblname set cs32_begin = @rstring , cs32_end = @cstring where UserID = 419
Here, litrals @cstring and @rstring do not have their values at output.
Please help. Thanks.

Whould help you would show us all the code, not just a snippet.

Also, the variable @cstring is being treated as a simply a string. If its value is supposed to be part of the concatentation, you need to change how you are doing it.

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

Hi Rovan,

Are you running PRINT @SQL to get that output?

Does the EXECUTE command not have the expected results?

Thanks

Gaz

RovanSQL SSC Enthusiast Points: 103 More actions · Answer 3

Thanks for the reply.

I want the value to be seen, not the literals to be concatenated.

How do i do it?

I've put a more detailed code now.

RovanSQL SSC Enthusiast Points: 103 More actions · Answer 4

Gaz:

Yes, i am using the PRINT command to see what i get.

If the output query is fine i will change it to EXECUTE.

Gazareth One Orange Chip Points: 27737 More actions · Answer 5

Thanks, they should be converted to their variable values by sp_executesql, PRINT will only show the variable as it is, e.g. @rstring.

As this happens within sp_executesql, you won't see it from the PRINT command.

If you want to check, convert the query to a select & run sp_executesql against that, and see if you get the expected results.

Hope that helps.

Cheers

WayneS SSC Guru Points: 95409 More actions · Answer 6

I'm assuming that your table has columns like:

cs1_start, cs1_end, cs2_start, cs2_end, etc.

Is there any particular reason that you didn't create a properly normalized, separate table for this data?

userId

courseId

start

end

(FK link back into parent table record on the userId and courseId columns)

This would make the query that you're trying to do very simple:

UPDATE t1

SET [start] = t2.registrationdate,

[end] = t2.completiondate

FROM tblnamechild t1

JOIN tblname t2

ON t1.userid = t2.userid

AND t1.courseId = t2.courseId

WHERE t2.userid = @userid;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

RovanSQL SSC Enthusiast Points: 103 More actions · Answer 7

RovanSQL (6/13/2012)
Gaz:
Yes, i am using the PRINT command to see what i get.
If the output query is fine i will change it to EXECUTE.

you mean the PRINT will only show the literals and not the real-time values?

and EXECUTE will execute it with the actual values?

RovanSQL SSC Enthusiast Points: 103 More actions · Answer 8

WayneS (6/13/2012)
I'm assuming that your table has columns like:
cs1_start, cs1_end, cs2_start, cs2_end, etc.
Is there any particular reason that you didn't create a properly normalized, separate table for this data?
userId
courseId
start
end
(FK link back into parent table record on the userId and courseId columns)
This would make the query that you're trying to do very simple:
UPDATE t1
SET [start] = t2.registrationdate,
[end] = t2.completiondate
FROM tblnamechild t1
JOIN tblname t2
ON t1.userid = t2.userid
AND t1.courseId = t2.courseId
WHERE t2.userid = @userid;

yes, the column names are dynamically generated. It had to be done that way for what i wanted to do.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 9

RovanSQL (6/13/2012)
Here's part of code i've been trying to run in a procedure, cursor is already defined:
DECLARE @form_dates CURSOR
SET @form_dates= CURSOR FOR
SELECT courseid, registrationdate, completeddate
FROM tblname
WHERE UserID = @tempID
ORDER BY courseID;
OPEN @form_dates
FETCH NEXT FROM @form_dates INTO @cid, @regdate, @compdate
/* PRINT @regdate
PRINT @compdate This is correctly having values in datetime. eg: Mar 28 2012 6:03PM
PRINT @cid */
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N'update tblname set cs' + CAST (@cid AS VARCHAR(10)) + '_begin = @rstring , cs' + CAST (@cid AS VARCHAR(10))+ '_end = @cstring where UserID = '+ CAST (@tempID AS VARCHAR(10))
DECLARE @ParmDefinition nvarchar(500) SET @ParmDefinition = N'@rstring datetime, @cstring datetime'
EXECUTE sp_ExecuteSQL @SQL, @ParmDefinition, @rstring = @regdate, @cstring = @compdate
I get this output
update tblname set cs32_begin = @rstring , cs32_end = @cstring where UserID = 419
Here, litrals @cstring and @rstring do not have their values at output.
Please help. Thanks.

You parameterized your variables (a good thing), so the print of @sql is only going to print the variable names and not the actual values. If you want to print the variable values, you have to print @regdate and @compdate.

My blog: http://jahaines.blogspot.com

Gazareth One Orange Chip Points: 27737 More actions · Answer 10

RovanSQL (6/13/2012)
RovanSQL (6/13/2012)
Gaz:
Yes, i am using the PRINT command to see what i get.
If the output query is fine i will change it to EXECUTE.
you mean the PRINT will only show the literals and not the real-time values?
and EXECUTE will execute it with the actual values?

Exactly that, yes. 🙂