T-SQL Conditional WHERE Clause

I started the day today with great ambition. I am tasked with removing hard coded SQL and change to use stored procedures. I made it through several stored procedures and was fat, dumb, and happy. Until… I was stumped with a problem I was unprepared to solve. The problem occurred in converting a bunch of case statements in the hard coded SQL.  The code looked something like this (changed for brevity)

Dim strSQL As New StringBuilder
strSQL.Append("SELECT TOP 300 cus_no ,cus_name, search_name, slspsn_no")
strSQL.Append("FROM MyTable")
If myValue1 IsNot Nothing Then
     strSQL.AppendFormat("WHERE value1 = '{0}' ", myValue1)
ElseIf myValue2 Nothing Then
      strSQL.AppendFormat("WHERE value2 = '{0}' ", myValue2)
ElseIf myValue3 IsNot Nothing Then
      strSQL.AppendFormat("WHERE value3 LIKE '{0}%' ", myValue2)
End If
If accountType > 0 Then
      strSQL.AppendFormat("AND value1 {0} IN (SELECT thisValue FROM MySecondTable", IIf(accountType = 1, "", "NOT"))
End If

So I decided to tackle the first if block first. I googled the hell out of conditional where clauses in T-SQL. After a little bit of reading and experimenting I finally decided to go with the COALESCE solution.  this was tricky because if a field is NULL and the parameter is NULL then COALESCE will not equal each other so for the three conditions in the first if block I came up with the following:

WHERE COALESCE(value1,' ') = COALESCE(@myValue1, COALESCE(value1,' '))
AND COALESCE(value2,' ') = COALESCE(@myValue2, COALESCE(value2,' '))
AND COALESCE(value3,' ') LIKE COALESCE(@myValue3, COALESCE(value3,' '))

This had the desired affect. Notice the first and third COALESCE in each line has the space.  This is how you account for the NULL = NULL situation.

Now on to what I consider the harder of the two types of conditional where clauses represented here. If accountType is supplied and it is equal to one then check to see if it is IN the list returned from the SELECT. If accountType is supplied and is greater than one then it should not be IN the SELECT statement. if accountType is not supplied then don’t bother adding it to the where clause. I found some great examples of bit comparisons in the google search but they all used equals instead of IN and I couldn’t get IN to work with the case statements but I combined a couple of good items into this solution:

AND ((@AccountType=1 AND value1 IN (SELECT thisValue FROM mySecondTable))
OR (@AccountType>1 AND value1 NOT IN(SELECT thisValue FROM mySecondTable))
OR (@AccountType<1 AND value1 = value1))

This covers all possible scenarios. Let me know if you have another way to accomplish this.

Till next time…

Technorati Tags: .NET,T-SQL,Conditional WHERE Clause,stored procedures,COALESCE