Tag: TVP

Pass multiple values to case when expression

There may be a time when you want to pass multiple values to a CASE WHEN expression. Here’s an example, take this simple data set below:

CREATE TABLE [Test]
([UserID]   INT, 
 [Approval] VARCHAR(10)
);

INSERT INTO [Test]
VALUES
       (1, 
        'Reject'),
       (2, 
        'Approve'),
       (3, 
        'test'),
       (4, 
        'test2'),
       (5, 
        'Approve'),
       (6, 
        'Reject');

SELECT 
       *
FROM [Test];
Our base data set we will use as an example.

Let’s say I wanted to pass a list of UserIDs to a stored procedure that is using a CASE WHEN statement to update our Test table. I want to update the column Approval and I want to set the statuses of <Reject> to <Rejected> and <Approve> to <Approved> when I pass the [UserID] and [Approval] column to the stored procedure. If I pass a UserID and a value not <Approve> or <Reject>, it should be ignored.

Continue reading “Pass multiple values to case when expression”