CODEWITHSUNDEEP
sqlsql-server
MumbaiKandivali
MumbaiKandivali

Reputation: 121

How to Use Dynamic Sql on Update Command?

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.

Upvotes: 0

Views: 62

Answers (3)

Abuzar Ansari
Abuzar Ansari

Reputation: 63

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)+'';

Upvotes: 1

Thom A
Thom A

Reputation: 95554

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.

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

The DBMS fails to add 

9801246

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

Upvotes: 1

Related Questions