CODEWITHSUNDEEP
sql-serverstored-procedures
Antoine Pelletier
Antoine Pelletier

Reputation: 3326

How to make a "two dependent parameters" SQL stored procedure

I just can't figure it out, what I mean by two dependents parameters is this :

Suppose I have records like these :

ID    Letter     Number
-----------------------
23    A          1
23    A          2
23    B          1
23    B          2
81    A          1
81    B          2

The user is to input this :

Then only ID 23 would be returned, because it's the only one that respect all these conditions :

A1, A2, B1, B2

Every time I tried some query, 81 was returned...

In the context of my question it would be quick enough to make 4 conditions like :

A and 1, A and 2, B and 1, B and 2

But imagine if i have 16 * 16 ...

It would be extremely long to write them all...

Upvotes: 0

Views: 321

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use COUNT DISTINCT in the HAVING clause:

SELECT Id
FROM #Tbl
WHERE
    Letter IN('A', 'B')
    AND Number IN(1, 2)
GROUP BY Id
HAVING
    COUNT(DISTINCT Letter) = 2
    AND COUNT(DISTINCT Number) = 2;

For more dynamic approach, you can put the criteria in table variables:

DECLARE @Letters TABLE(Letter CHAR(1));
DECLARE @Numbers TABLE(Number INT);

INSERT INTO @Letters VALUES ('A'), ('B');
INSERT INTO @Numbers VALUES (1), (2);

WITH CteCross(Letter, Number) AS(
    SELECT Letter, Number 
        FROM @Letters
        CROSS JOIN @Numbers
)
SELECT t.Id
FROM #Tbl t
INNER JOIN CteCross cc
    ON cc.Letter = t.Letter
    AND cc.Number = t.Number
GROUP BY t.Id
HAVING COUNT(*) = (SELECT COUNT(*) FROM CteCross);

ONLINE DEMO

Upvotes: 4

Related Questions