CODEWITHSUNDEEP
sql
Mark Highfield
Mark Highfield

Reputation: 453

Conditional filter in WHERE clause

How can I set a conditional filter in a SQL WHERE clause? For example, I have a parameter @ID with the following procedure

SELECT * FROM myTable WHERE Column1 = 'test' AND Column2 = @ID

However, If @ID = -1 I don't want the last part of the SQL (AND Column2 = @ID) included

I realize I can make an if statement with 2 separate queries, however this is a large script and has this same issue multiple times, so I was hoping there was a better way than nearly duplicating several queries

Upvotes: 2

Views: 264

Answers (3)

user359040
user359040

Reputation:

One alternative:

SELECT * FROM myTable WHERE Column1 = 'test' AND @ID in (-1,Column2)

Upvotes: 1

Chris Moutray
Chris Moutray

Reputation: 18369

Just include the condition in your SQL as an OR, note the brackets

SELECT * FROM myTable WHERE Column1 = 'test' AND (@ID = -1 OR Column2 = @ID)

Upvotes: 2

Silvermind
Silvermind

Reputation: 5944

This is ok for T-SQL: 

SELECT * FROM myTable WHERE Column1 = 'test' AND (@ID = -1 OR Column2 = @ID)

Upvotes: 5

Related Questions