I was working on a search form for my current project and ran into a problem with trying to pass arrays of values via a SQL Parameter. 

Let's say the main object is an Order (so you can parallel this with the Northwind sample database).  There are various stages to an Order, first it is submitted, then received, then shipped, then closed (for example).  Now the customer wants to be able to search on Order states such as "All unshipped orders".  This means the Order can be in a state of submitted or received.  Or maybe the customer wants to search on "All open orders", which corresponds to any state that is not closed.  The problem is SQL Server Parameters only allow you to pass in a single value, like submitted.

Luckily, I came across the SQL Server Magazine article Treat Yourself to Fn_Split() by Burton Roberts.  In the article, he gives you the code to a user-defined function (UDF) that will split a string on a given character, such as a comma, and create a table variable to hold the array of strings.  The UDF returns a table, which coincidentally is allowed in an IN operator in the WHERE clause of a SQL statement.

So if a customer wants to see all unshipped orders, I set the parameter @StatusList equal to "Submitted,Received".  Then, I pass this parameter in to my SQL query (most likely a stored proc), and in the WHERE clause I add the line:

WHERE (OrderStatus IN ((select value from dbo.fn_Split(@StatusList,','))) OR @StatusList IS NULL)

where OrderStatus is the column name that holds the status of the order record (Submitted, Received, etc.).  The second argument of fn_split is the character you want to split on.  This can be any character.

Now I can pass everything into my stored proc (or SQL statement) via SQL parameters, which is a more secure and more performant than using dynamic SQL.