x

Datetime and sp_executesql

I'm trying to run this code:

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, literals @cstring and @rstring do not have their values at output. Please help. Thanks.

more ▼

asked Jun 13, 2012 at 04:01 PM in Default

RohanSQL gravatar image

RohanSQL
10 1 1 2

So far everything looks good. Where do you have a problem? If you were to print your dynamic sql variable, it would print the update statement which includes 2 parameters which are fed to the statement when you run your execute, so it should work as is.
Jun 13, 2012 at 07:00 PM Oleg
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

As @Oleg has already pointed out, it is working as it should be. sp_ExecuteSQL would assign the values to the params at runtime while execution. But it would not change the content of your dynamic SQL variable @SQL. So if you are printing your dynamic SQL after/before execution, it will still print it the same. If you want to see the values printed as well then you need to tweak your dynamic sql something like

SET @SQL = 
    N'update tblname set cs' + CAST (@cid AS VARCHAR(10)) + 
    '_begin = '''+ CONVERT(VARCHAR, @rstring, 126) + ''' , cs' + CAST (@cid AS VARCHAR(10) )+ 
    '_end = '''+ CONVERT(VARCHAR, @cstring, 126) + ''' where UserID = ' + CAST (@tempID AS VARCHAR(10))
more ▼

answered Jun 14, 2012 at 07:37 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x292
x242
x38
x24

asked: Jun 13, 2012 at 04:01 PM

Seen: 1373 times

Last Updated: Jun 14, 2012 at 07:37 AM