sqlsql-serverstored-procedurestable-valued-parameters

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


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.


Solution

  • 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';