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…
WHERE…
…AND
FieldName IN
(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 ) + ’%’