Is it possible to pass in multiple values in the WHEN expression

Question

I am trying to update a bunch of rows and their approval status, I have something like this

UPDATE table
SET [Approval] =
CASE 
WHEN @approvalStatus = 'Approve' then 'Approved'
WHEN @approvalStatus = 'Reject' then 'Rejected'
END
WHERE [User ID] = @userID

I wanted something like this:

UPDATE table
SET [Approval] =
CASE 
WHEN {1} = 'Approve' then 'Approved'
WHEN {1} = 'Reject' then 'Rejected'
END
WHERE [User ID] IN ({0}) AND [Approval] IN ({1})

Is there something like this possible

Edit:

Sample Data:

{ "User ID": "1", "Approval": "Approve", "Name": "John" }
{ "User ID": "2", "Approval: "Reject", "Name": "Bob" }

What I would pass in {0} would be ('1','2') and for {1} would be ('Approve', 'Reject')

Answer 1

I believe to have written a query that meets your request below:

DECLARE @table TABLE (UserID INT, Approval VARCHAR(10))

INSERT INTO @table
VALUES (1, 'Reject'), (2, 'Approve')

DECLARE @approvalStatus VARCHAR(10)
DECLARE @UserID INT

SET @approvalStatus = 'Approve'
SET @UserID = 2

UPDATE @table
SET Approval = CASE 
        WHEN @approvalStatus = 'Approve'
            THEN 'Approved'
        WHEN @approvalStatus = 'Reject'
            THEN 'Rejected'
        END
FROM @table
WHERE Userid = (@UserID)
    AND Approval = (@approvalStatus)

SELECT *
FROM @table

I created a fake variable table and inserted some similar data as your example illustrates and then I have updated the data in the table and I finally do a select from it to show the value has changed.

Here's the MSDN on CASE WHEN, it will have more examples and more information for you to follow.

Notice how I changed your WHERE clause? This is because with a stored procedure it is hard to pass an array as a variable which is what you are attempting. If you want to make it an array you will need to use Table Valued Parameters.

I modified an answer by Nick Chammas on DBA Stack Exchange. They are using TVP's to pass an array. Here is a code example of your situation and how to pass an array into a stored procedure to update your table. Give this a try in a test environment where you can safely create and drop objects without impacting important systems.

-- Using a staging table now, table variables need to be seen when calling the proc. 
-- Most of these pieces are one time runs, run the steps 1-4. Afterwards, the code is repeatable to run
-- and you can adjust the parameters you pass to the TVP to see the effects. I have added more sample
-- code to the table so you can see how it interacts with the data.

-- Step (1)
CREATE TABLE Test (UserID INT, Approval VARCHAR(10))

-- Step (2)
-- Creating test records
INSERT INTO Test
VALUES (1, 'Reject'), (2, 'Approve'), (3, 'test'), (4,'test2'), (5,'Approve'), 
(6,'Reject')

-- Step (3)
-- This is our TYPE used for the TVP
CREATE TYPE id_list AS TABLE (
    id INT --NOT NULL PRIMARY KEY
    , approvaltwo VARCHAR(10)
    );
GO

-- Step (4)
-- This is a procedure you would use to update data
CREATE PROCEDURE [dbo].[tvp_test] (
    @customer_list id_list READONLY
    )
AS
BEGIN
-- Update the table
    UPDATE test
    SET Approval = CASE 
            WHEN approval = 'Approve'
                THEN 'Approved'
            WHEN approval = 'Reject'
                THEN 'Rejected'
            -- This line updates with what was there previously. Without the ELSE, it would null
            -- cells that are not 'Reject' or 'Approve'
            ELSE Approval
            END
    FROM test t
    -- Joining on TVP to filter rows that should be updated
    INNER JOIN @customer_list cl
        ON t.userid = cl.id
            AND t.approval = cl.approvaltwo;
END;
GO

-- The rest of the steps are now repeatable
-- This is where your code would begin now
-- Declare your TVP
DECLARE @customer_list id_list;

-- Insert into the TVP, this is where you pass your array
INSERT INTO @customer_list (id, approvaltwo)
VALUES (1, 'Reject'), (2, 'Approve'), (3,'test');

-- Execute against the TVP and table
EXECUTE [dbo].[tvp_test] @customer_list = @customer_list;
GO

-- View results
SELECT *
FROM Test

One last note is that you may have to adjust your array in code before passing it to the TVP.