CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
A G
A G

Reputation: 22559

SQL Server - optional where clause - empty Table Valued Parameters

I have three TVP - A, B and C

A is never empty. B or C can be empty.

AND
SomeId IN (SELECT n FROM @A) -- First
AND
SomeId IN (SELECT n FROM @B) -- Second -- Make this optional
AND
SomeId IN (SELECT n FROM @C) -- Third -- Make this optional

I need to make the Second/Third condition optional. I have tried quite a lot of things like case, (SELECT .. OR @B = null), but since these are table value parameters its not working.

Upvotes: 0

Views: 891

Answers (6)

tep
tep

Reputation: 833

It would be much cleaner and equally as succinct to do the following:

;WITH AllowedIds_CTE (SomeId) AS (
    SELECT n from @A
    UNION
    SELECT n from @B
    UNION
    SELECT n from @C
)
SELECT * FROM SomeTable O
JOIN AllowedIds_CTE A
    ON O.SomeId = A.SomeId;

Multiple nested subqueries and IN operations are less readable and potentially less efficient than joins with CTEs/tables.

The CTE (Common Table Expression) in this model can be replaced with an indexed temp table if performance is a concern, and UNION can be replaced with UNION ALL if you know your n values to be distinct across @A, @B and @C.

Upvotes: 1

Solomon Rutzky
Solomon Rutzky

Reputation: 48776

Try the following. It starts off as the same query as the original, but for each of the two "optional" TVPs it adds a UNION ALL to select the "SomeId" ONLY IF there are now rows in that particular TVP. This keeps with the intent of using the TVP as a filter IF there are rows in it, else it does not filter out the row.

DECLARE @Main TABLE (ID INT);
INSERT INTO @Main (ID) VALUES (55);
INSERT INTO @Main (ID) VALUES (999);

DECLARE @TestA TABLE (Col1 INT);
INSERT INTO @TestA (Col1) VALUES (55);
INSERT INTO @TestA (Col1) VALUES (67855);
DECLARE @TestB TABLE (Col1 INT);
--INSERT INTO @TestB (Col1) VALUES (565);

SELECT tmp.ID
FROM @Main tmp
WHERE tmp.ID IN (SELECT Col1 FROM @TestA)
AND tmp.ID IN (
               SELECT Col1 FROM @TestB
               UNION ALL
               SELECT tmp.ID
               WHERE NOT EXISTS(SELECT * FROM @TestB)
              )

The query (with the INSERT INTO @TestB commented out) returns the value of 55 since @TestB is "optional". But if you uncomment that INSERT INTO @TestB, then nothing is returned since @TestB does not contain the value 55.

Upvotes: 1

Kevin Cook
Kevin Cook

Reputation: 1932

SELECT m.*, a.N, b.N, c.N FROM MyTable m
INNER JOIN @A a
    ON a.N = m.SomeId
LEFT JOIN @B b
    ON b.N = m.SomeId
LEFT JOIN @C c
    ON c.N = m.SomeId

So you are trying to get all the records that match to a, and then if they match to b or c, then you can do something with them?

Upvotes: 1

DWF
DWF

Reputation: 360

I have seen and set stored procedures where the parameters are defaulted to NULL

@A int = NULL,
@B int = NULL,
@C int = NULL

The code behind will load in parameters, and @B and @C may or may not be loaded.

When you get to your WHERE clause, you put

SomeId IN (SELECT n FROM @A)
AND (SomeID IN (SELECT n FROM @B) OR @B IS NULL)
AND (SomeID IN (SELECT n FROM @C) OR @C IS NULL)

Notice the syntax; it is not @B = NULL, it is @B IS NULL

Upvotes: 1

Raj More
Raj More

Reputation: 48018

If only the second and third conditions are optional, then try this

AND SomeId IN (SELECT n FROM @A) -- First

OR

(
    SomeId IN (SELECT n FROM @B) -- Second -- Make this optional
    OR
    SomeId IN (SELECT n FROM @C) -- Third -- Make this optional
)

Upvotes: 1

Jesuraja
Jesuraja

Reputation: 3844

Try using OR

AND
(
SomeId IN (SELECT n FROM @A) -- First
OR
SomeId IN (SELECT n FROM @B) -- Second -- Make this optional
OR
SomeId IN (SELECT n FROM @C) -- Third -- Make this optional
)

Upvotes: 1

Related Questions