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.