asked
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.
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))
10 People are following this question.
