Help with creating a SQL query with optional multi-value parameters

Ok this is my way, but it's not the only way. I pass multi-value parameters to an SQL query as a comma-delimited string. Then I use a SQL function that take the comma-delimited string and returns a table variable. This is elegant because you can just "join" straight to the result of the function in one line. There is an excellent example (including the code for the conversion function) http://www.sql-server-helper.com/functions/comma-delimited-to-table.aspx Your search would ends up looking something like this: SELECT Users.* FROM   [dbo].[Users] INNER JOIN   [dbo].[myStringToTableFunc] ( "NY,NJ,CT" ) State     ON Users.[StateD] = State.[String] WHERE   Users.[Title] = "Stud Muffin"

Dammit just re-read your question and saw the bit about the "optional" State parameter. This might get ugly. I would also go straight towards a dynamic SQL approach, with a test for a null parameter, but this is not nice from an optimisation point of view. Another option is to set default values for optional parameters in a stored proc. So you would have something like: CREATE PROCEDURE myProc ( @Name varchar(255) = "%%", @States varchar(1000) = "AK,CA,NY...list of ALL states", @Title varchar(255) = "%%" ) Then you can use "Like" clauses to match on Name and Title, and the "%%" will match ANYTHING if no value is passed. The full states list will return every result regardless of state if you pass nothing. To reverse the logic (e.g. return nothing if no param is passed), use an empty string as the defaults.

Better = simpler. I'm not sure how a non-XML-based solution could achieve more flexibility, but I'm open to seeing how. http://ask.metafilter.com/65656/Help-with-creating-a-SQL-query-with-optional-multivalue-parameters#986621- I don't want to use CSV because it requires extra data manipulation before sending off and it doesn't get around the optional issue- INNER JOIN [dbo].[myStringToTableFunc] ( "" ) will always return 0 rows. I do use that technique when I'm only dealing with one parameter, but it's not flexible IMO because you need to define each parameter separately in the SP.

To clarify- my issue isn't with single-entry parameters (name, title, etc.), because there will ALWAYS be one value passed, even if it's just an empty string (yes, I'm using LIKE for that)- it's with parameters which can occur 0+ times, the way HTML checkbox form elements are passed.

Gah yeah sorry I'll just crawl back under my rock. I totally misinterpreted your question. http://msdn2.microsoft.com/en-us/library/ms345117.aspx has a little ditty on how to extract xml values as a table (about half way down), which gets you part way thre, but you're back to a dynamic query if the table is empty.