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
SET Approval = CASE
WHEN @approvalStatus = 'Approve'
WHEN @approvalStatus = 'Reject'
WHERE Userid = (@UserID)
AND Approval = (@approvalStatus)
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'),
-- 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)
-- Step (4)
-- This is a procedure you would use to update data
CREATE PROCEDURE [dbo].[tvp_test] (
@customer_list id_list READONLY
-- Update the table
SET Approval = CASE
WHEN approval = 'Approve'
WHEN approval = 'Reject'
-- This line updates with what was there previously. Without the ELSE, it would null
-- cells that are not 'Reject' or 'Approve'
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;
-- 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;
-- View results
One last note is that you may have to adjust your array in code before passing it to the TVP.