A common pattern in applications is to have a search stored procedure work as the backend for a search screen. If a term has not been passed by the user, the intent is that there is no filter on the results for that parameter. Here's a great article discussing the ways that this can be acheived. I'm a personal fan of the COALESCE statement talked about in the static discussion.
One area that was not discussed in the article was the idea of having similar functionality with a list of filters (e.g. filter to these specific books in the library). I found a pretty good article talking about SQL 2005's use of Xml parameters to do this - Xml's main benefit being that it's more designed for this use case than a delimited string. However, the same problem now applies...what if I either have a list of books, or don't pass anything, meaning I want to search across all books?
My solution was to grab all the data from the reference table and build my own Xml string. Then I could use it in the main select statement:
IF @param is null
SET @param =
(SELECT 1 AS Tag,0 AS Parent,MyElementName AS [Root!1!string!element]
FROM sourcetable FOR XML EXPLICIT)
(SELECT Field.value('.','VARCHAR(max)') FinalName
FROM @param.nodes('//string') AS Table(Field))
I'm sure this wouldn't perform well with large tables, but my reference tables are fairly small (<100 rows each), and it works great.
FYI: Here is an example COALESCE statement for the standard filters:
table LIKE '%' + COALESCE(@Name, fac.FacilityName ) + '%'