1

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')

  • could you include some sample data? and example fill in values for {0} and {1}? – Jonathan Fite Jun 28 '18 at 15:20
  • I added sample data – Nabz Jun 28 '18 at 15:28
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.

  • Hey thanks for answering. I tried out your solution the query runs without any errors but it is not updating my data, its not changing the Approval – Nabz Jun 28 '18 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.