CODEWITHSUNDEEP
sqlsql-server
user6820529
user6820529

Reputation:

How can I filter by a subset of conditions based on a parameter in Microsoft SQL?

I have a parameter in an SQL query where I would like to select either a subset of possible rows, or one based on the parameter.

Basically, I have a column called model, which can contain either 1000, 2000, 3000, 4000, or 5000. When the parameter is set to NULL, I would like it to select just 1000, 2000 and 3000, but not 4000 or 5000.

I have tried various combinations of CASE statements, I.e.

WHERE model = CASE WHEN @model = NULL THEN model = 1000 OR model = 2000 OR 
model = 3000 ELSE @model END AND <query continues>

How can I achieve this in SQL Server?

Upvotes: 0

Views: 295

Answers (2)

ViKiNG
ViKiNG

Reputation: 1334

try one of the following:

Where (model = @model OR 
(@model IS NULL AND model <> model IN (1000,2000,3000,4000))

OR

Where (model = @model OR (@model IS NULL AND model <> model <> 5000)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Just do this using or:

WHERE ( (@model is null and model in (1000, 2000, 3000)) or
        model = @model
      )

Upvotes: 0

Related Questions