SQL Conditional Where
Posted by
Luis Majano
May 13, 2010 17:01:00 UTC
Today I needed to create a conditional where statement in MSSQL and I had no idea how? It was an incoming variable to a stored procedure and I needed to ignore it in my SQL if the value was 0, if it was not 0 then I needed to do a where statement as a list. The solution was so simple and elegant I was like WOW!! I like that.
My first thought was to do a if-else split on the SQL with the incoming variable like this:
1: if @i = 1
2: select * from mytable where mycol = 1
3: else
4: select * from mytable where mycol = 2
However, this does not hold true to DRY principles, so the solution I ended up using was the following with a simple OR statement!
1: AND(
2: @docType = 0
3: OR
4: d.docType in (@docType)
5: )
A simple OR statement made my life much easier, so there you go, simple conditional statements when you at least expect certain values on incoming variables.
Tags: Development, MSSQL
Josh Highland
you are a SQL guru!
Gus
You can simplify this even further by just using: "AND d.docType in (0@docType)"
When @docType == 0 the statement ends up being AND d.docType in (00) which will return docType of 0
When @docType == 1,2,3 the statement ends up being AND d.docType in (01,2,3) which will return docTYpes of 1,2 3
This is working on the assumption that docType is an int.
Luis Majano
Gus, does this work in sql 2000?
Gus
Yes, this will work in SQL 2000 - 2008
Lu Sancea
Gus, how are you sending that list into a proc? I don't see how that would work.