Select * from table where column in %

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

            select 'NY'

--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 --comment this line to see @allStates flip

            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