ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming

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

Updated on October 28, 2011

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.



Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.