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