--comment this line to see @allStates flip
Monday, July 6, 2009
This method allows you to filter a column based on a list supplied by the user or when the user doesn't supply anything to return all the results without using dynamic SQL. You can use ANDs in the where clause to include additional filters. In our application we parse a comma delimited string in SQL to get our list of user input. SQL 2005 introduced table variables and you could probably use those instead.
--pretend this table contains all the states.
declare @states table (identifier varchar(2))
insert into @states
select 'MI' UNION
select 'CO' UNION
--this simulate states selected in the application
declare @selectedStates table (identifier varchar(2))
--insert a state to simulate the user selecting a state.
insert into @selectedStates
select 'MI' --comment this line to see @allStates flip
declare @allStates bit
--if the user hasn't selected a state get all the results.
if ((select count(*) from @selectedStates) = 0)
select @allStates = 1
--give back the results:
select identifier from @states
where (identifier in (select identifier from @selectedStates) OR @allStates = 1) AND 1=1 --additional filters could go here instead of 1=1