CODEWITHSUNDEEP
sql-server-2008
RajVish
RajVish

Reputation: 191

SQL server: where clause for optional fields

Track Id is optional field in my application. Case 1: If I pass valid track id, it should return the respective rows. Case 2: If invalid track id is passed, no rows should be returned. Case 3: If no track id passed, all rows should be returned

DECLARE @transaction_ID INT     
SELECT @transaction_ID = Transaction_ID FROM myTable WHERE TRACK_ID= @Track_Id

My where condition is:

WHERE (@transaction_ID IS NULL OR myTable.Transaction_ID = @transaction_ID)            
  AND (amount<>0)

with the above condition 1 and 3 cases are working fine. but 2nd case got failed. When I passed invalid track id, all rows are getting returned. Please correct the query to handle the case 2. Thanks in advance.

Upvotes: 0

Views: 219

Answers (2)

Sigar Dave
Sigar Dave

Reputation: 2654

Try something like this

Where 1=(Case when @TrackId = 1 Or TRACK_ID= @Track_Id then 1 else 0 end )

When you want all the data then pass 1 ,

When want data as per TrackID pass @trackID value and when you dont wnat the condition to be applied pass null

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

Just continue to query @Track_ID as well:

WHERE (
       (@transaction_ID IS NULL AND @Track_Id IS NULL) OR
       myTable.Transaction_ID = @transaction_ID)            
AND (amount<>0)

(The only situation where you want a NULL @Transaction_ID to make this WHERE clause succeed is case 3. In Case 2, a non-null @Track_Id was passed but @Transaction_ID will be NULL because no rows were returned, so that's the situation we're trying to deal with)

Upvotes: 1

Related Questions