Stored procedure with CASE and WHERE coming from a table value parameter

Question

In a SQL Server stored procedure, can I use two separate columns from a table-valued parameter (TVP) in both the CASE and WHERE clauses?

In my TVP, I have two columns like so:

CREATE TYPE tt_Index AS TABLE
                        (
                            referenceType varchar(20),
                            referenceID varchar(20)
                        )

In the stored procedure, how can I iterate over the table parameter and use referenceType within a CASE, and referenceID within a WHERE clause?

Something like this:

CREATE PROCEDURE usp.Test
    @parIndexTable  tt_Index READONLY
AS
    SELECT 
        CASE (@parIndexTable.referenceType)
            WHEN 'ref1' THEN (SELECT * FROM NamesCurrent nc 
                              WHERE @parIndexTable.referenceID = nc.referenceID)
            WHEN 'ref2' THEN (UPDATE NamesCurrent nc 
                              SET nc.Name = 'Craig' 
                              WHERE @parIndexTable.referenceID = nc.referenceID)
        END

From what I've understood, I need to iterate over the TVP as a table but just unsure of the syntax for that and how it fits in with the case statement so I can use both parameters.

Answer 1

I must admit, the logic you have seems odd here, as you have a SELECT and an UPDATE and you want to SELECT from that UPDATE(?). As you want both a SELECT and an UPDATE on different thing, you need to statements here; one for the SELECT which doesn't UPDATE anything and another for the UPDATE, which assume also needs an OUTPUT clause.

To use the TVP, you just need to use a JOIN and you can filter the rows in the SELECT/UPDATE in the WHERE.

This results in the following statements:

SELECT NC.{Explicit List of Columns}
FROM dbo.NamesCurrent NC
     JOIN @parIndexTable pIT ON NC.referenceID = pIT.referenceID 
WHERE pIT.referenceType = 'ref1';

UPDATE NC
SET Name = 'Craig'
OUTPUT NC.{Explicit List of Columns} --I assume you want this too?
FROM dbo.NamesCurrent NC
     JOIN @parIndexTable pIT ON NC.referenceID = pIT.referenceID 
WHERE pIT.referenceType = 'ref2';