CODEWITHSUNDEEP
sqlsql-serverdynamic
kresa
kresa

Reputation: 135

dynamic SQL update command

I am using MSSQL 2016,

I need to be able to update a row on a table dynamically.

I got a stored procedure :

CREATE PROCEDURE sp_lookupData_UpdatelookupValues
 (
 @FullTableName nvarchar(50), 
 @Id nvarchar(10),
 @Name nvarchar(50),
 @Description nvarchar(50)
 )
AS
BEGIN

DECLARE @Cmd nvarchar(150) = N'UPDATE ' + @FullTableName + ' SET Name = ' +  @Name  + ', Description = ' + @Description + ' WHERE ID = ' + @Id + '';

EXECUTE sp_executesql @Cmd;

END

The problem is that Name and Description values are passed into the @Cmd like this : 

UPDATE TABLE_NAME SET Name = Private, Description = Default WHERE ID = 1

Instead of 'Private' and 'Default'.

The result is an error where Private is being counted as a column which doesnt exist ( because of the bad format ). 

Invalid column name 'Private'.

Upvotes: 1

Views: 13311

Answers (3)

Hybris95
Hybris95

Reputation: 2400

Put the quotes yourself

Use single quotes around Private and Default.
And since you are using dynamic querying, you have to double the single quotes to escape them.

DECLARE @Cmd nvarchar(150) = N'UPDATE ' + @FullTableName + ' SET Name = ''' +  @Name  + ''', Description = ''' + @Description + ''' WHERE ID = ' + @Id + '';

Also make sure you try the next solution, since the first one is SQL Injection compatible.

Use sp_executesql parameters

You can also use the parameters inside your @Cmd without doing the concatenation yourself but by passing the parameters to sp_executesql

Also I suggest you to QUOTENAME the @FullTableName parameter in case of spaces inside table's name.

DECLARE @Cmd nvarchar(150) = N'UPDATE QUOTENAME(@FullTableName) SET Name = @Name, Description = @Description WHERE ID = @Id;'
EXEC sp_executesql @Cmd, @FullTableName, @Name, @Description, @Id;

The advantage doing so, is you avoid any parameters not checked by the application to be able to do SQL Injection.

Reference :

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You cannot use parameters for identifiers. But, you can use parameters for values:

DECLARE @Cmd nvarchar(150) = N'
UPDATE ' + @FullTableName + '
    SET Name = @Name,
        Description = @Description 
WHERE ID = @Id';

EXECUTE sp_executesql @Cmd,
        N'@Name nvarchar(50), @Description nvarchar(50), @Id nvarchar(10)',
        @Name = @Name, @Description = @Description, @Id = @id;

Unfortunately, the dynamic table name still poses risks, both in terms of SQL injection and syntax errors. I am guessing this is "controlled" code, not code with user input, so the risks might be acceptable. However, you probably should use quotename().

That brings up another issue which is probably the crux of the problem. Why do you have multiple tables with the same columns? Could these -- should these -- all be stored in a single table? This type of code calls into question aspects of the data model.

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15140

You have to add the quotes yourself. I prefer to use QUOTENAME to keep all the quotes recognizable:

QUOTENAME(@FullTableName, '''')

Upvotes: 0

Related Questions