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.

You **CANNOT** put entire code blocks inside the `THEN` part of a `CASE`. `CASE` is an **expression** that returns a single atomic value - you cannot use it to conditionally execute one code block or another — marc_s, Feb 22 '23 at 14:59
What you want is impossible even with normal tables. `CASE` is an expression, not a statement, and can't contain statements like `SELECT` or `UPDATE`. A table must appear in a FROM clause. — Panagiotis Kanavos, Feb 22 '23 at 14:59
Besides, SQL, the language, is set based. You don't iterate over rows, you write a query that does something, eg INSERT/UPDATE/DELETE with the results of the query. You can use `MERGE` to insert or update rows into a target table, although that's no more efficient than two separate statements, one UPDATE to update existing rows and an INSERT to insert new ones — Panagiotis Kanavos, Feb 22 '23 at 14:59
Thanks for this info. In my limited wisdom, I guess I only wanted to ‘iterate’ through the tvp once, and perform an action based on each row of the tvp depending on what the reference was. Sounds like I’ll have to do run my query many times against the same tvp and just join my records to get what I need. — Craig, Feb 22 '23 at 15:11
@Craig When using SQL try to avoid thinking about "iteration" (because SQL doesn't have loops), instead recall back to your high-school level math classes about set-theory because SQL is all about working with sets (specifically, [sets of tuples](https://en.wikipedia.org/wiki/Relational_algebra)). And I'm highly doubtful that you will need to run your query many times... the way to use TVPs in queries is usually to JOIN them once (e.g. for an exclusion filter, use an _anti-join_), also consider `CROSS APPLY`. — Dai, Feb 22 '23 at 15:16

score 2 · Accepted Answer · answered Feb 22 '23 at 15:27

2

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

answered Feb 22 '23 at 15:27

Thom A

88,727
11
45
75

Thank you Larnu. It’s clear to me now that return submits the comment and doesn’t not instil a carriage return. Following on from my single comment of thanks I did add further gratitude and seek further clarity but I’ve given up . I will delete this question now. Sorry to have taken up your time. Regards – Craig Feb 22 '23 at 15:42
You can't delete a question that has an upvoted answer, @Craig , and I would (personally) urge against deleting the question; of your undeleted questions only one has upvotes, and the rest all a score of 0 or less. Deleted questions with a score of <= 0 are weighted much more heavily by the algorithm towards rate limiting a user from asking further questions. I can't see how many deleted questions you have, but you could find that deleting this question could be enough to "break the ice" and land you into a question ban. – Thom A Feb 22 '23 at 15:49
What question remains, @Craig ? *If* my answer doesn't answer the question, as your prior (now deleted) comment implied it did, then explain why it doesn't. – Thom A Feb 22 '23 at 15:57
Sorry, not having a good day. Meant I won’t delete the question. – Craig Feb 22 '23 at 18:35

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

1 Answers1