Is it possible to update a variable field in a stored procedure?

Question

I have a simple query that updates a variable column. This query is later string.Formatted and passed to SqlCommand (c#) (TableId is a column in SomeTable):

"UPDATE SomeTable set {0}=@Value where TableId=@TableId"

I need to convert this query to a stored procedure. Is it possible at all?

Answer 1

I'm making a few assumptions about what you're doing, but evaluate this code.

CREATE PROCEDURE UpdateTableValue
    (
     @tableId INT
    ,@columnName sql_variant
    ,@value VARCHAR(10)
    )
AS 
    BEGIN

        DECLARE @sql NVARCHAR(MAX)
        SET @sql = N'UPDATE SomeTable '
                   + N'SET ' + QUOTENAME(@columnName) + N' = = @value '
                   + N'WHERE TableId = @tableId'

     EXEC sp_executesql @sql
             , N'@value sql_variant, @tableId int'
             , @value, @tableId;

    END

Answer 2

It can only be done through dynamic SQL in the stored procedure (as Brad has alreayd answered, but of course you'd use QUOTENAME(@columnName) to properly protect against SQL injection, and use a sysname parameter type). If you go down that path, I recommend you read up first The Curse and Blessings of Dynamic SQL.

Updated:

I had to post code, since Brad's code has just too many mistakes.

create procedure myCustomUpdate
   @columnName sysname,
   @value sql_variant,
   @id int
AS
declare @sql NVARCHAR(max);
set @sql = N'UPDATE SomeTable SET ' + QUOTENAME(@columnName) 
    + N' = @value WHERE TableId = @id';
exec sp_executesql @sql, N'@value sql_variant, @id int', @value, @id;

Answer 3

I think there is a better solution in the article that Remus mentioned: The Curse and Blessings of Dynamic SQL . I ended up using CASE switches to optimize performance (dynamic SQL is not being cached) like in the article:

UPDATE tbl
SET    col1 = CASE @colname WHEN 'col1' THEN @value ELSE col1 END,
       col2 = CASE @colname WHEN 'col2' THEN @value ELSE col2 END,