question

sindhu avatar image
sindhu asked

replacing expression with dyamically created temp table

DECLARE @UpdateColumns NVARCHAR(MAX) = replace(Stuff((Select ' ,T.['+ Convert(Varchar(100),i.reward_user_extra_type ) +'] = isnull( S.[' + Convert(Varchar(100), i.reward_user_extra_type) +']' + ' ,T.['+ Convert(Varchar(100),i.reward_user_extra_type ) +'])' From #inserted i where i.rowid = @u for xml path('')), 1, 2, ''),' ','') How do I replace the #inserted with [##' + @TmpGlobalTable + '] table in above expression?Please help me out.
sqlsqlserver 2008dynamic sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
Because the temp table name is a variable, you have to generate dynamic SQL to populate the column list whereas with known table name you could just execute the select statement. You need a variable to store the script which can be executed. The **sp\_executesql** does accept parameters (both in and out), so the original @UpdateColumns variable can be used for the out parameter. Here is the script which should produce desired output (replace of the carriage return is not needed in my version because I use char(10) instead of hitting Enter key which caused the carriage returns to be entitized by xml path. I still left the replace there just in case): declare @updateColumnsSql nvarchar(max); declare @updateColumns varchar(max); select @updateColumnsSql = 'select @updateColumnsOut = replace(stuff(( select '', ' + char(10) + 'T.['' + convert(varchar(100), i.reward_user_extra_type ) + ''] = isnull(S.['' + convert(varchar(100), i.reward_user_extra_type ) + ''], T.['' + convert(varchar(100), i.reward_user_extra_type ) + ''])'' from [##' + @tmpGlobalTable + '] i where RowID = ' + cast(@u as varchar) + ' for xml path('''')), 1, 3, ''''), '' '', ''''); '; exec sp_executesql @updateColumnsSql, N'@updateColumnsOut varchar(max) out', @updateColumnsOut = @updateColumns out; -- uncomment to see results --select @updateColumns; Whenever there is a need to translate the static SQL to dynamic, the first step might be to highlight the original script and replace every occurrence of the single quote with the pair of single quotes via Ctrl + H. After that the resulting string can be wrapped into single quotes. This produces the good starting point because the resulting string is already a valid script. It would be easier to opt for wrapping the updates/inserts into transaction, as discussed in the [answer to one of your earlier questions][1], so there would be no necessity to define a dynamically named global temp table, but this is your call. Hope this helps, Oleg [1]: https://ask.sqlservercentral.com/questions/143867/is-it-safe-to-use-global-temporary-table-in-trigge.html
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Oleg I will try doing wrapping inserts/updates into transaction. This is getting more complex using dynamically named global temp table.Thank You!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.