Passing an array of values to a stored procedure in SQL 2005

Question

Does T-SQL accomodate for array values as parameters for stored procedures? If so how can this be achieved.

Answer 1

What I prefer to use instead is a comma (or other special character) separated list which I will split/explode first thing in my sproc. This will then give me a table of values to work with and that I can then join on or perform other actions on later on in my stored procedures.

You can also look into passing in table parameters, but I kind of like my way more just as a personal preference.

Answer 2

You can find a few dupes of this question, here is one: http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values/43767#43767

Answer 3

It was often achieved passing in a CSV, which is obviously limited. With SQL 2005 an Xml parameter may be much better suited, with a serializer suited to your needs perhaps.

There may be more and I'll come back if I think of any.

Answer 4

T-SQL dialect in SQL Server 2005 does not support neither arrays, nor anything similar, so they have to be emulated. SQL Server 2008, however, supports table-valued parameters, which can be used as arrays.

Answer 5

Duplicate of http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values/43767#43767 and http://stackoverflow.com/questions/617706/passing-an-in-list-via-stored-procedure

The first url has a link to the definitive answer.

Answer 6

There are no arrays in sqlserver. However, What are you trying to do? What would the values as parameters be used for? You can send sql an "array" but the sp would have to be dynamic.