CODEWITHSUNDEEP
sqlsql-servert-sql
user2112420
user2112420

Reputation: 985

SQL Server If condition at where clause?

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!!!

Upvotes: 0

Views: 2183

Answers (7)

Mihai-Daniel Virna
Mihai-Daniel Virna

Reputation: 994

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

Upvotes: 0

user2261062
user2261062

Reputation:

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

Upvotes: 0

D Stanley
D Stanley

Reputation: 152521

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)

Upvotes: 3

Rahul
Rahul

Reputation: 77866

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)

Upvotes: 0

David
David

Reputation: 218818

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.

Upvotes: 0

Hellmar Becker
Hellmar Becker

Reputation: 2972

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)

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

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

Upvotes: 0

Related Questions