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

wdebmtf2 于 2023-02-28 发布在其他

关注(0)|答案(1)|浏览(100)

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.

sql-server

来源：https://stackoverflow.com/questions/75534282/stored-procedure-with-case-and-where-coming-from-a-table-value-parameter

1条答案

按热度按时间

oug3syen1#

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

赞(0）回复(0）举报 2023-02-28

我来回答

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

1条答案

相关问题

热门标签

最新问答