Datetime and sp_executesql

Question

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.

Answer 1

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))

Datetime and sp_executesql

Follow this question

Related Questions