SQL Server If condition at where clause?

Question

I would like to make a select query, I have several parameters but depending on the value I add the parameter to the where clause. I do not want to use dynamic query. For example this is my stored declaration:

EXEC GETProducts @ProductID INT = -1, @ProductName NVARCHAR(100) = NULL, @ProductManufacturerID INT = -1

I want all the products manufactured by ManufacturerID = 3

EXEC GETProducts -1, NULL, 3

I would like a query like this, I know it does no work, I also tried with CASE, but not working.

SELECT * FROM Product
WHERE 
IF(@ProductID > -1)
BEGIN
 ProductID = @ProductID
END
AND
IF(@ProductName <> '')
BEGIN
 ProductName = @ProductName 
END
AND
IF(@ProductManufacturerID > -1)
BEGIN
 ProductManufacturerID = @ProductManufacturerID 
END

Thanks for your help!!!

Show what you tried with `CASE` since that will be much closer to a working solution than `IF`. — D Stanley, Jan 21 '16 at 15:37
Also if you can try to show what you start with and what your output and expected output is. — Mihai-Daniel Virna, Jan 21 '16 at 15:38
A case statement seems reasonable in this situation over the `IF` statement just because of how messy your code might get — Mark Hill, Jan 21 '16 at 15:39
[Dynamic Search Conditions in T-SQL](http://www.sommarskog.se/dyn-search.html) by Erland Sommarskog covers a lot of options — Damien_The_Unbeliever, Jan 21 '16 at 15:41
@MarkHill `IF` is completely invalid since it's for program flow, not expression evaluation, and can't be used inside a `WHERE` clause. — D Stanley, Jan 21 '16 at 15:42
@DStanley oh, yes you're right, duh. Brain fart! I need my coffee this morning. — Mark Hill, Jan 21 '16 at 15:44
Possible duplicate of [SQL: IF clause within WHERE clause](http://stackoverflow.com/questions/87821/sql-if-clause-within-where-clause) — Tab Alleman, Jan 21 '16 at 16:00

D Stanley · Answer 1 · 2016-01-21T15:44:39.127

3

It sounds like you want a combination of ANDs and ORs instead of CASE or IF:

SELECT * FROM Product
WHERE 
(@ProductID <= -1 OR ProductID = @ProductID)
AND
(@ProductName = '' OR ProductName = @ProductName)
AND
(@ProductManufacturerID <= -1 OR ProductManufacturerID = @ProductManufacturerID)

However I would note that NULL is generally preferred to "magic" numbers and strings for special cases:

SELECT * FROM Product
WHERE 
(@ProductID IS NULL OR ProductID = @ProductID)
AND
(@ProductName IS NULL OR ProductName = @ProductName)
AND
(@ProductManufacturerID IS NULL OR ProductManufacturerID = @ProductManufacturerID)

edited Jan 21 '16 at 15:44

answered Jan 21 '16 at 15:40

D Stanley

149,601
11
178
240

Using OR, wouldn't he select cases in which only one of the two considitions meet? Supose that @PruductID is -3 and ProductID is 5. Since the clause (@ProductID <= -1 OR ProductID = @ProductID) evaluates true, it will actually select it. – Jan 21 '16 at 15:53
@BlackAdder Correct. The intent is likely to use any negative number as a "magic number" that would select ALL products (effectively to NOT filter by product ID). I'm guessing in this case but it's a common use case. – D Stanley Jan 21 '16 at 16:35

score 0 · Answer 2 · answered Jan 21 '16 at 15:40

0

SELECT * 
FROM Product
WHERE 
    (@ProductID = -1 OR ProductID = @ProductID)
AND (@ProductName = '' OR ProductName = @ProductName)
AND (@ProductManufacturerID = -1 OR ProductManufacturerID = @ProductManufacturerID)

answered Jan 21 '16 at 15:40

Juan Carlos Oropeza

47,252
12
78
118

score 0 · Answer 3 · answered Jan 21 '16 at 15:40

0

I am guessing a bit here. You probably want to write:

WHERE 
(@ProductID > -1 AND ProductID = @ProductID)
OR
(@ProductName <> '' AND ProductName = @ProductName )
OR
(@ProductManufacturerID > -1 AND ProductManufacturerID = @ProductManufacturerID)

answered Jan 21 '16 at 15:40

Hellmar Becker

2,824
12
18

score 0 · Answer 4 · answered Jan 21 '16 at 15:41

You can make "conditional" WHERE clauses by pairing them with OR clauses. Something like this:

SELECT * FROM Product
WHERE
    (@ProductID <= -1 OR ProductID = @ProductID)
    AND @ProductName = '' OR ProductName = @ProductName)
    -- etc.

That way if the "conditional check" is true than the second half of the OR clause isn't evaluated.

score 0 · Answer 5 · answered Jan 21 '16 at 15:42

You can change your query to be like below by making the conditions to be composite condition and you don't need a CASE expression. BTW, IF .. ELSE construct works only within a procedural body.

SELECT * FROM Product
WHERE (@ProductID > -1 AND ProductID = @ProductID)
AND (@ProductName <> '' AND ProductName = @ProductName)
AND (@ProductManufacturerID > -1 AND ProductManufacturerID = @ProductManufacturerID)

score 0 · Answer 6 · answered Jan 21 '16 at 15:46

You can translate the conditionals into requirements inside the WHERE clause.

IF(@ProductID > -1)
BEGIN
    ProductID = @ProductID
END

is equivalent to:

ProductID > -1 AND ProductID = @ProductID

,

IF(@ProductName <> '')
BEGIN
    ProductName = @ProductName 
END

is equivalent to:

ProductName <> '' AND ProductName = @ProductName

,

IF(@ProductManufacturerID > -1)
BEGIN
    ProductManufacturerID = @ProductManufacturerID 
END

is equivalent to:

ProductManufacturerID > -1 AND ProductManufacturerID = @ProductManufacturerID

So your final Query will be:

SELECT * FROM Product WHERE 
ProductID > -1 AND
ProductID = @ProductID AND
ProductName <> '' AND
ProductName = @ProductName AND
ProductManufacturerID > -1 AND
ProductManufacturerID = @ProductManufacturerID

Your "equivalents" are not correct - e.g. if `@ProductID` is less than -1 then there would be NO filter on `ProductID`. Your equivalent would remove ALL records if `@ProductID` is less than -1. — D Stanley, Jan 21 '16 at 16:38
I misunderstood what @user2112420 wanted to achieve. To me, it looked like he wanted first to check if a product has ID > -1 and then check if it corresponds to the ID in the variable. — , Jan 21 '16 at 16:43

Mihai-Daniel Virna · Answer 7 · 2016-01-21T15:54:13.660

0

The when your parameter is intended to affect the select query the case will evaluate to 1 therefore making it part of the select statement, if not it will be 0 therefore it will not count because 0=1 will evaluate to false.

SELECT * FROM Product
WHERE 
(CASE 
     WHEN @ProductId > -1 AND ProductId = @ProductId THEN 1 
     ELSE 0
 END) = 1
AND 
(CASE 
     WHEN @ProductName <> '' AND ProductName = @ProductName THEN 1 
     ELSE 0
END) = 1
AND 
(CASE
     WHEN @ProductManufacturerID > -1 AND ProductManufacturerID = @ProductManufacturerID THEN 1 
END);

edited Jan 21 '16 at 15:54

answered Jan 21 '16 at 15:48

Mihai-Daniel Virna

984
9
19

1

You miss the part when comparing the @parameter with the row value – Juan Carlos Oropeza Jan 21 '16 at 15:50
@JuanCarlosOropeza Edited my answer, it should be fine now. – Mihai-Daniel Virna Jan 21 '16 at 15:54

SQL Server If condition at where clause?

7 Answers7