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

Question

Possible Duplicate:
T-SQL stored procedure that accepts multiple Id values

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

Duplicate of [http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values/43767#43767](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](http://stackoverflow.com/questions/617706/passing-an-in-list-via-stored-procedure) The first url has a link to the definitive answer. — adrianbanks, Jun 15 '09 at 13:23
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 — ahains, Jun 15 '09 at 13:22

score 2 · Answer 1 · answered Jun 15 '09 at 13:23

2

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.

answered Jun 15 '09 at 13:23

Anton Gogolev

113,561
39
200
288

score 1 · Answer 2 · answered Jun 15 '09 at 13:22

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.

answered Jun 15 '09 at 13:22

TheTXI

37,429
10
86
110

My mistake, table value parameters were introduced in 2008, not 2005. – TheTXI Jun 15 '09 at 13:24

score 0 · Answer 3 · answered Jun 15 '09 at 13:23

0

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.

answered Jun 15 '09 at 13:23

dove

20,469
14
82
108

score 0 · Answer 4 · answered Jun 15 '09 at 13:24

0

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.

answered Jun 15 '09 at 13:24

Eric

7,930
17
96
128

There are plenty of ways to do it which does not require you to write "dynamic SQL" – TheTXI Jun 15 '09 at 13:25
1

Sql does support arrays. In fact, arrays are pretty central to it's being. Sql just calls arrays tables. – Wyatt Barnett Jun 15 '09 at 13:26

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

4 Answers4