TSQL Parameterized SPROC question

Question

I need to write a stored procedure to update one of a set of similar columns. The columns are named 'UserField1', 'UserField2' etc. I was hoping to pass a parameter to the SPROC which would set the column to be updated. However, I can't seem to get the code correct. Here's a simplified example of what I tried (which gets me an 'Incorrect syntax' message):

create procedure UpdateUserField
    (@UserFieldNumber int, @UserFieldNewValue int)
as
    update MyTable set
        case @UserFieldNumber
        when 1 then UserField1
        when 2 then UserField2
        end
    = @UserFieldNewValue

Answer 1

I don't think you can use CASE statements to in field lists in statements. I'm almost positive you can only apply CASE statements to the values your are setting, returning or testing.

In your example you are trying to modify the field list using a case statements and I am assuming SQL has problems with it because the field list won't be determined until after the statement is compiled and run, which is kind of a chicken and egg situation.

Answer 2

What about using a number of IF's?

CREATE PROCEDURE UpdateUserField
(
 @UserFieldNumber int,
 @UserFieldNewValue int
) AS

IF @UserFieldNumber=1
BEGIN
 UPDATE MyTable SET UserField1 = @UserFieldNewValue
END

IF @UserFieldNumber=2
BEGIN
 UPDATE MyTable SET UserField2 = @UserFieldNewValue
END

Alternatively you can build dynamic SQL in an exec

CREATE PROCEDURE UpdateUserField
(
 @UserFieldNumber int,
 @UserFieldNewValue int
) AS

EXEC('UPDATE MyTable SET UserField' + CONVERT(varchar(10), @UserFieldNumber) + ' = ' + CONVERT(varchar(10), @UserFieldNewValue))

Beware SQL Injection if you do this though, with ints you won't have a problem, anything else you may need to consider risks.

Answer 3

if @UserFieldNumber=1
BEGIN
    update MyTable set UserField1=@UserFieldNewValue where...
END
ELSE if @UserFieldNumber=2
BEGIN
    update MyTable set UserField2=@UserFieldNewValue where...
END
ELSE if @UserFieldNumber=3
BEGIN
    update MyTable set UserField3=@UserFieldNewValue where...
END

Answer 4

The update command doesn't accept dynamic field names.

Instead, what you can do is build a string with the update statement that you want to execute, and then execute it, like this:

DECLARE @strToExecute VARCHAR(1000)
SET @strToExecute = 'UPDATE MyTable '
IF @UserFieldNumber = 1 
  SET @strToExecute = @strToExecute + ' SET UserField1 = '
IF @UserFieldNumber = 2
  SET @strToExecute = @strToExecute + ' SET UserField2 = '

And so on to build your string, and then execute it.

Answer 5

AFAIK you can't use a CASE WHEN to specify the Column.

You could wrap this up in Some Dynamic SQL though.

CREATE PROCEDURE UpdateUserField
( 
    @UserFieldNumber int, 
    @UserFieldNewValue int
)
AS

DECLARE @sql nvarchar(max)
SET @sql = 'UPDATE MyTable SET UserField' + @UserFieldNumber + ' = ' + @UserFieldNewValue
EXEC (@sql)

Answer 6

If you don't want to use dynamic SQL or multiple IF and UPDATE statements then you could try something like this instead:

UPDATE MyTable
SET UserField1 = CASE WHEN @UserFieldNumber = 1
        THEN @UserFieldNewValue ELSE UserField1 END,
    UserField2 = CASE WHEN @UserFieldNumber = 2
        THEN @UserFieldNewValue ELSE UserField2 END