How-To Add a T-SQL (transact-sql) CASE Expression to a WHERE Clause

How To include a SQL Case Expression in a Where Clause


I see so many blog entries about this subject. Many seem to struggle with how to add a Case When operation to a Where clause but it actually quite simple. For simple Where clauses you could write them as:



Select col1 from table
where col2 = case when a < b then c else d end.



For more complex Where clauses most programmers don’t know that you can include a scalar function in the Where clause and add any level of complexity and return a value. Take for example the following bit of code:



Select col1 from table
where col2 = GetValueFunction(col3, col4)



The Function would look something like this:



GetValueFunction
(
@colval1 int,
@colval2 int
)
return int
Begin
declare @returnVal int

select @returnVal = case when @colval1 < b then c

when @colval2 > f then g

else h

end


return @returnVal
end



You can add much more complex expressions that would either be impossible or very messy directly in a Where clause. This way you can pack a lot of Case when conditions and return a value to use in your Where clause. Additionally you could add further processing with boolean expressions to further refine your Where clause.

It makes your program much more modular and neater then trying to add a lot of Case expressions directly to the Where clause. Of course this technique can work for any type of SQL program that you may want to write, it isn’t just reserved for Case When operations.



More by this Author


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working