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

avatar 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

avatar image

Usman Butt
13.9k 6 12 21

(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.

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:

x2072
x389
x273
x56
x29

asked: Jun 13, 2012 at 04:01 PM

Seen: 1695 times

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

Copyright 2016 Redgate Software. Privacy Policy