CODEWITHSUNDEEP
sqlsql-servert-sql
ontranet
ontranet

Reputation: 11

SQL SELECT multiple criterias IF not null

I have a dataset where i want to select the records that matches my input values. But i only want to try matching a field in my dataset against the input value, if the dataset value is not NULL.

I always submit all 4 input values. @Tyreid, @CarId,@RegionId,@CarAgeGroup

So for the first record in the dataset i get a successful output if my input values matches RegionId and CarAgeGroup.

I cant figure out how to create the SQL script for this SELECT?

My dataset

TyreId  CarId   RegionId    CarAgeGroup
------  -----   --------    -----------
NULL    NULL    1084            2
65      35      1084            1
5       35      1084            1
NULL    41      1085            NULL
120     NULL    NULL            NULL
NULL    NULL    1084            2
65      NULL    1084            NULL

Upvotes: 0

Views: 966

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272396

Keeping in mind that NULL = <any value> yields NULL you need to check for NULL column values separately:

WHERE (TyreId      IS NULL OR TyreId      = @Tyreid)
AND   (CarId       IS NULL OR CarId       = @CarId)
AND   (RegionId    IS NULL OR RegionId    = @RegionId)
AND   (CarAgeGroup IS NULL OR CarAgeGroup = @CarAgeGroup)

Upvotes: 1

jpw
jpw

Reputation: 44921

Maybe this is what you want?

SELECT * FROM YourTable 
WHERE (TyreId = @Tyreid OR TyreId IS NULL) 
    AND (CarId = @CarId OR CarId IS NULL) 
    AND (RegionId = @RegionId OR RegionId IS NULL) 
    AND (CarAgeGroup = @CarAgeGroup OR CarAgeGroup IS NULL)

Sample SQL Fiddle

Upvotes: 0

bummi
bummi

Reputation: 27384

You might use ISNULL or Coalecse to reach the expected result, if the Field is NULL the parameter will be compared with the parameter.

Select * from aTable
Where ISNULL(Tyreid,@Tyreid)=@Tyreid
AND   ISNULL(CarId ,@CarId )=@CarId 
AND   ISNULL(RegionId,@RegionId)=@RegionId
AND   ISNULL(CarAgeGroup,@CarAgeGroup)=@CarAgeGroup

Upvotes: 2

Related Questions