SQL Server Dynamic WHERE clause without exec

Learn how to use SQL Server CASE statements in WHERE clauses to avoid the use of dynamic SQL.

Here's a quick little tip for searching a database based on passed in criteria that may or may not have been passed in by the user. Put the following in Query Analyzer and test it against the Pubs database with @LastName populated with or without "White". You'll find that if @LastName isn't populated, it simply looks for records with the lastname equal to itself which is always true.


use pubs

declare @LastName as nvarchar(100)

select @LastName = 'White'

select au_id,
au_fname,
au_lname
from Authors
where au_lname = case
when DataLength(@LastName) > 0 then @LastName
else au_lname
end


Submission Date:  9/23/2005 2:45:05 PM
Submitted By:  Robbe Morris
My Home Page:  http://www.robbemorris.com