CODEWITHSUNDEEP
sqlsql-serverselectcasewhere-clause
emeksc1
emeksc1

Reputation: 53

Where in case in SQL Server

If @IsPrice=0, ProductPrice=0 will return. If not, ProductPrice > 0 will return. This is what I want, but I could not write the code exactly. 

declare @Isprice bit=0

select * 
from [Product] p 
where case when @Isprice = 0 then p.ProductPrice = 0 else p.ProductPrice > 0 end

Upvotes: 1

Views: 72

Answers (2)

user3680659
user3680659

Reputation: 13

If you want to keep the CASE logic you could also consider:

SELECT *
FROM   [Product] p 
WHERE  
1 = CASE
WHEN (@Isprice = 0  AND p.ProductPrice = 0) THEN 1
WHEN (@Isprice <> 0 AND p.ProductPrice > 0) THEN 1
ELSE 0
END;

Upvotes: 1

Mureinik
Mureinik

Reputation: 311163

A case expression returns a value, not an SQL fragment. You could get this behavior by using some logical operators, though:

SELECT *
FROM   [Product] p 
WHERE  (@Isprice = 0  AND p.ProductPrice = 0) OR
       (@Isprice <> 0 AND p.ProductPrice > 0)

Upvotes: 2

Related Questions