Arrays and Lists in SQL Server

Erland Sommarskog - SQL Server MVP wrote a great article about using Arrays and Lists in SQL Server.

The problem

You have a number of key values, identifying a couple of rows in a table, and you want to retrieve these rows. If you are composing the SELECT statement in the client code, you might have some code that looks like this:

  SQL = "SELECT ProductID, ProductName FROM Northwind..Products " & _
  "WHERE ProductID IN (" & List & ")"   rs = cmd.Execute(SQL)

List is here a variable which you somewhere have assigned a string value of a comma-separated list, for instance "9, 12, 27, 39".

Now you want to use stored procedures. However, you don't seem to find that any apparent way of doing this. Some people try with:

   CREATE PROCEDURE get_product_names @ids varchar(50) AS
   SELECT ProductID, ProductName      
   FROM   Northwind..Products      WHERE  ProductID IN (@ids)

Which they then try to call:

   EXEC get_product_names '9, 12, 27, 37'

But this fails with:

   Server: Msg 245, Level 16, State 1, Procedure get_product_names,Line 2
   Syntax error converting the varchar value '9, 12, 27, 37'
   to a column of data type int.

This fails, because we are no longer composing an SQL statement dynamically, and @ids is just one value in the IN clause. An IN clause could also read:

   ... WHERE col IN (@a, @b, @c)

Others that run into the problem say to themselves: If T-SQL has arrays like any other normal language, then this is not a problem. Well, T-SQL does have arrays. They are called tables, and for all matters they are much more general in nature than arrays. If all you want is plain integer-indexed array with a single value in each cell, this is easily emulated in a table. However, you cannot pass a table as a parameter to a stored procedure. When one stored procedure calls another stored procedure this is not much of a problem, because they can agree on a common table to use for their interchange. This is not possible when you call a stored procedure from a client. But: a client can pass a string, and in SQL you can unpack that string into a table, and most of the methods I describe in this article use some technique to do this.

For the solutions to this problem read his article here