CODEWITHSUNDEEP
sqlsql-servert-sql
Kevin
Kevin

Reputation: 57

SQL Server CASE statement in where clause dependent on column

I have a stored procedure with a select statement and I am trying to add a case statement that if the type is column [si.TYPE] is NEW then it will filter based on if the MSRP is between 2 values and if the column [si.TYPE] is USED it will filter based on if the SELLINGPRICE is between 2 values.

There error is showing on the first BETWEEN:

Incorrect syntax near BETWEEN

Code:

SELECT .... 
FROM .... 
WHERE.....

     AND CASE si.[type]
         WHEN 'new' THEN si.[msrp] BETWEEN @priceMin AND @priceMax 
         WHEN 'used' THEN si.[sellingprice] BETWEEN @priceMin AND @priceMax
     END;

Will I need to do 2 separate selects and then UNION the results to achieve this? It works in MySQL just need to find the solution in SQL Server

Upvotes: 0

Views: 609

Answers (1)

user743382
user743382

Reputation:

In SQL Server, you have boolean expressions, and you have non-boolean expressions. The syntax determines which type it is, and boolean expressions cannot be used where non-boolean expressions are expected. MySQL allows them to be mixed more freely. You'll have to rework your syntax; one possible way would be

 SELECT .... 
 FROM .... 
 WHERE.....
 AND (si.[type] = 'new' AND si.[msrp] BETWEEN @priceMin and @priceMax
      OR si.[type] = 'used' AND si.[sellingprice] BETWEEN @priceMin and @priceMax)

Upvotes: 2

Related Questions