CODEWITHSUNDEEP
sqlsql-servert-sql
Jonathan
Jonathan

Reputation: 651

Update table using parameters

I have a stored procedure that gets these parameters:

@TaskId        UNIQUEIDENTIFIER,
@ColumnName    VARCHAR(255) = NULL,
@CheckBoxValue VARCHAR(255) = NULL

Then I have an UPDATE statement like this:

UPDATE [RedMarkItems]
SET @ColumnName = @CheckBoxValue
WHERE [TaskId] = @TaskId

If I run my stored procedure like:

exec usp_RedMarkItem_Insert 
         @TaskId = '82ab0c4b-9342-46fa-acbe-c00b87571bf9', 
         @ColumnName = Item7, 
         @CheckBoxValue = 1, 
         @CurrentUser = '6074caea-7a8e-4699-9451-16c2eaf394ef'

It does not affect table just says

Commands completed successfully

But values still the same, but if I replace values in UPDATE statement like

UPDATE [RedMarkItems]
SET Item7 = 1
WHERE [TaskId] = '82ab0c4b-9342-46fa-acbe-c00b87571bf9'

It works! Why is it not working if I use parameters? Regards

Upvotes: 1

Views: 2759

Answers (2)

Conor Cunningham MSFT
Conor Cunningham MSFT

Reputation: 4501

What you did was set @ColumnName equal to the value in @CheckBoxValue 0 or more times (based on how many rows exist in the table). Likely not what you intended...

Instead, you will either want to use dynamic SQL (set @sql = 'UPDATE … ' + QUOTENAME(@ColumnName) + 'rest of sql') or otherwise build a case statement to handle each column based on the value you are trying to update dynamically. SQL needs to bind the statement at compile time, so you need to have that available at compile time for the query processor to make sure that the column is real, has the right types to do type derivation, etc. Using parameters as you did would prevent all of that logic from working (assuming the semantics were as you intended in your posted question).

Please be careful as SQL injection attacks are possible on non-validated parameters. You would want to make sure that the column name is a valid column name and not something that allows arbitrary SQL code execution under the context of the running transaction.

Upvotes: 2

Thom A
Thom A

Reputation: 95950

The mistake you're making here is you're under the impression that a variable/parameter can be used in place for an object's name. Simply put, it can't. An object's name must be a literal, so you can't do something like:

DECLARE @TableName sysname;
SET @TableName = N'MyTable';

SELECT *
FROM @TableName;

For things like this you need to use dynamic SQL, and (just as importantly) secure dynamic SQL.

Firstly, I would change @ColumnName to the datatype sysname (which is a synonym for nvarchar(128)) and @Checkbox to a bit (A Checkbox can have only 3 values: True/1, False/0 and NULL, so an nvarchar(255) is a very poor data choice). Then your Stored Procedure will look something like:

DECLARE @SQL nvarchar(MAX);

SELECT @SQL = N'UPDATE RedMarkItems' + NCHAR(10) +
              N'SET ' + QUOTENAME(c.[name]) + N' = @Checkbox' + NCHAR(10) +
              N'WHERE TaskID = @ID;'
FROM sys.tables t
     JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.[name] = @ColumnName
  AND t.[name] = 'RedMarkItems';

EXEC sp_executesql @SQL, N'@Checkbox bit, @ID uniqueindentifier', @Checkbox = @CheckBoxValue, @ID = @TaskId;

The reason for the references to the sys tables is to ensure that the column does indeed exist. If it does not, then no SQL will be run. This is just an extra safety measure, alongside the use of QUOTENAME.

Upvotes: 5

Related Questions