question

sindhu avatar image
sindhu asked

How I make this dynamic sql statement work?

Hello, My update statement is as below: this doesn't work if NULL value or 'up'p' value is in @extravalue.How can I make this work?Please help me out.Thanks! declare @query_string nvarchar(max) = ' update c set c.' + @extra_type + ' = '''+ cast(@extra_value as nvarchar(max)) + ''' from ws_user_flat_t_'+ cast(@Companyid as varchar(20)) + ' c inner join #inserted i on i.reward_user_id = c.id where c.id = '+ cast(@user_id as varchar(20)) + ' ' print @query_string Execute SP_ExecuteSQL @query_string
sql-server-2008sql
6 comments
10 |1200

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

@sindhu I posted the answer to take care of all scenarios, but unfortunately, it went into moderation. It will become available once it is cleared by the moderators. I am not sure why this keeps happening to some of my answers.
1 Like 1 ·
I mean if any string has apostrophe it doesn't work.What change should I make?
0 Likes 0 ·
I added this before the update statement it works.But how to make it work for NULL values? set @extra_value = REPLACE(@extra_value, '''', '''''')
0 Likes 0 ·
Is this for SQL 2005 or 2008? Or both? Both are tagged.
0 Likes 0 ·
Thanks Oleg! Will check once it's available. John I updated the tags.
0 Likes 0 ·
Show more comments

1 Answer

·
Oleg avatar image
Oleg answered
If the variable named @extra_value is null then the whole @query_string is set to null due to **concat\_nulls\_yields\_null** setting. Also, you assume that the variable is a string, but this is not necessarily so because it could be something else which does not require single quotes around it. In fact, if the value is null then you want the resulting SQL to read ***set SomeColumn = null*** rather than ***set SomeColumn = 'null'***. To prevent unexpected results, just modify this variable and the script like this: if the value is null then just spell out null (no single quotes), otherwise, add single quotes around the value. If the variable has any single quotes as a part of its value then every occurrence of the single quote must be replaced with 2 single quotes in order not to break the resulting dynamic script. In any case, remove the extra single quotes (which are not needed with this approach) from around the variable setting part of the @query_string. Here is the script which will work regardless of whether the variable named @extra_value is null or not, and has any single quotes as a part of it's value: select @extra_value = case when @extra_value is null then 'null' else '''' + replace(@extra_value, '''', '''''') + '''' end; declare @query_string nvarchar(max) = ' update c set c.' + @extra_type + ' = ' + cast(@extra_value as nvarchar(max)) + ' from ws_user_flat_t_'+ cast(@Companyid as varchar(20)) + ' c inner join #inserted i on i.reward_user_id = c.id where c.id = '+ cast(@user_id as varchar(20)) + ' ' print @query_string; --execute sp_executesql @query_string; Hope this helps, Oleg
10 |1200

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

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.