How to Use Dynamic Sql on Update Command?

Question

I Had Update Command. Just Like this.

Update DispatchExceptions
SET TYPE='MWM'
WHERE ID=9801246

I want to fire same command using dynamic sql.

DECLARE @ColumnName varchar(20)=N'TYPE'
DECLARE @ColumnValue char(3)='MWM'
DECLARE @ID INTEGER = 9801246
declare @Query nvarchar(max)


SET @Query = 'Update DispatchExceptions SET '+ QUOTENAME(@ColumnName) + '''=' + @ColumnValue + '''WHERE ID =' + @ID +''

EXEC (@Query)

But it show following error.

Conversion failed when converting the nvarchar value 'Update DispatchExceptions SET [TYPE]'=MWM'WHERE ID =' to data type int.

How can I use dynamic sql in the situation. Any suggestion.

Looks like you've got your quote on the wrong side of the equals i.e. it should be [TYPE]='MWM' not [TYPE]'=MWM'. And something similar for your ID. — Dale K
– Dale K, Commented Oct 24, 2018 at 9:21
Well done on the use of QUOTENAME on @ColumnName , but @ColumnValue and @ID should be parametrised, not concatenated. — Thom A
– Thom A ♦, Commented Oct 24, 2018 at 9:23

Thom A · Accepted Answer · 2018-10-24 09:32:45Z

3

The problem here is specifically the part ' + @ID. @ID is the datatype int, which has a higher datatype precedence than nvarchar; thus any other expressions will be implicitly cast to an int (and as I'm sure you're aware, 'Update DispatchExceptions SET...' isn't an int).

If you parametrise your query, you won't have this problem:

DECLARE @ColumnName sysname = N'TYPE'; --Changed the datatype here to sysname, as the caters for all possible object names
                                       --sysname is actually a synonym for nvarchar(128), and 128 is the maximum length for an object's name
DECLARE @ColumnValue char(3) = 'MWM';
DECLARE @ID integer = 9801246;
DECLARE @Query nvarchar(MAX);   

SET @Query = N'Update DispatchExceptions SET ' + QUOTENAME(@ColumnName) + N' = @value WHERE ID = @ID;';

EXEC sp_executesql @Query,
                   N'@Value char(3), @ID int',
                   @Value = @ColumnValue,
                   @ID = @ID;

Also, to the OP, well done on the use of QUOTENAME. This is far too often missed out, and thus leaves your (dynamic) SQL open to injection.

edited Oct 24, 2018 at 9:32

answered Oct 24, 2018 at 9:27

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thorsten Kettner Over a year ago

That's a much better solution.

MumbaiKandivali Over a year ago

Thank's its helps Lot.

Thom A Over a year ago

You're welcome @MumbaiKandivali. If it solves the problem, please mark it as a solution so that others with the same problem will know that this helped you solve the problem.

Thom A · Accepted Answer · 2018-10-24 09:28:38Z

1

The DBMS fails to add

to

Update DispatchExceptions SET [TYPE]'=MWM'WHERE ID =

because the latter is a string and canot be converted to number, which would be necessary in order to add the number 9801246 to it :-)

One solution:

DECLARE @ID varchar(7) = '9801246'

Another solution:

SET @Query = 'Update DispatchExceptions SET '+ QUOTENAME(@ColumnName) +
             '''=' + @ColumnValue + '''WHERE ID =' + CAST(@ID AS VARCHAR(7)) +''

edited Oct 24, 2018 at 9:28

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

answered Oct 24, 2018 at 9:25

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Comments

Abuzar Ansari · Accepted Answer · 2018-10-24 10:04:53Z

1

Simply cast your @ID as varchar and correct quotation before @ColumnValue as given below:

SET @Query = 'Update DispatchExceptions SET '+QUOTENAME(@ColumnName)+'='''+@ColumnValue+''' WHERE ID ='+cast(@ID as varchar)+'';

answered Oct 24, 2018 at 10:04

Abuzar Ansari

638 bronze badges

Collectives™ on Stack Overflow

How to Use Dynamic Sql on Update Command?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related