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
– D Stanley, Commented Jan 21, 2016 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
– Mihai-Daniel Virna, Commented Jan 21, 2016 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
– Mark Hill, Commented Jan 21, 2016 at 15:39
Dynamic Search Conditions in T-SQL by Erland Sommarskog covers a lot of options — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 21, 2016 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
– D Stanley, Commented Jan 21, 2016 at 15:42

D Stanley · Accepted Answer · 2016-01-21 15:44:39Z

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, 2016 at 15:44

answered Jan 21, 2016 at 15:40

D Stanley

153k12 gold badges190 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2261062 Over a year ago

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.

D Stanley Over a year ago

@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.

Juan Carlos Oropeza · Accepted Answer · 2016-01-21 15:40:00Z

0

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

answered Jan 21, 2016 at 15:40

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Comments

Hellmar Becker · Accepted Answer · 2016-01-21 15:40:28Z

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, 2016 at 15:40

Hellmar Becker

3,05215 silver badges19 bronze badges

Comments

David · Accepted Answer · 2016-01-21 15:41:43Z

0

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.

answered Jan 21, 2016 at 15:41

David

221k42 gold badges246 silver badges338 bronze badges

Comments

Rahul · Accepted Answer · 2016-01-21 15:42:09Z

0

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)

answered Jan 21, 2016 at 15:42

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Comments

user2261062 · Accepted Answer · 2016-01-21 15:46:58Z

0

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

answered Jan 21, 2016 at 15:46

user2261062

2 Comments

D Stanley Over a year ago

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.

user2261062 Over a year ago

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.

Mihai-Daniel Virna · Accepted Answer · 2016-01-21 15:54:13Z

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, 2016 at 15:54

answered Jan 21, 2016 at 15:48

Mihai-Daniel Virna

9949 silver badges20 bronze badges

2 Comments

Juan Carlos Oropeza Over a year ago

You miss the part when comparing the @parameter with the row value

Mihai-Daniel Virna Over a year ago

@JuanCarlosOropeza Edited my answer, it should be fine now.

Collectives™ on Stack Overflow

SQL Server If condition at where clause?

7 Answers 7

2 Comments

Comments

Comments

Comments

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

Comments

Comments

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related