T-SQL: pass an array of ids to a stored procedures

Question

I'm trying to send and array of parameters to a stored procedure

SELECT [id_Curso] AS IDCurso
  ,[Cod_Estabelecimento] AS CodEstabelecimento
  ,[Des_Estabelecimento] AS DesEstabelecimento
  ,[Cod_Curso] AS CodCurso
  ,[Des_Curso] AS DescCurso
  ,[Cod_Grau] AS CodGrau
  ,[Des_Grau] AS DescGrau
  ,[Cod_Area_Educacao] AS CodAreaEducacao
  FROM [BEP_DEV].[dbo].[Curso]
  where [Cod_Area_Educacao] in @List

DECLARE @List VARCHAR(MAX);
SELECT @List = '(1,2,3,4)';

SELECT [id_Curso] AS IDCurso
  ,[Cod_Estabelecimento] AS CodEstabelecimento
  ,[Des_Estabelecimento] AS DesEstabelecimento
  ,[Cod_Curso] AS CodCurso
  ,[Des_Curso] AS DescCurso
  ,[Cod_Grau] AS CodGrau
  ,[Des_Grau] AS DescGrau
  ,[Cod_Area_Educacao] AS CodAreaEducacao
  FROM [BEP_DEV].[dbo].[Curso]
where [Cod_Area_Educacao] in (1,2,3,4)

How can I transform the first case in something like the 2nd one (which works.)?

I tried also with xml but also can't make it work.

Any help?

possible duplicate of [T-SQL stored procedure that accepts multiple Id values](http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values) — mayabelle, Oct 11 '13 at 22:42
check the first link on right http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values?rq=1 — Iłya Bursov, Oct 11 '13 at 22:42

score 13 · Answer 1 · answered Oct 11 '13 at 23:36

There are a number of possibilities for that, but given the you're on SQL 2008 the number one choice would be to use the new table valued parameters, in which you can send a whole table to a query or stored procedure in a single go (in your case, a table with a single column with an arbitrary number of IDs).

First create a table type in your database:

CREATE TYPE idTable AS TABLE (id INT)

Then just declare your procedure with a parameter of that type, and use it like any other table:

CREATE PROCEDURE SelectList (@IDs idTable READONLY) AS
SELECT * FROM sometable
INNER JOIN @IDs AS idTable ON idTable.id=sometable.id

There is a great article that discusses this and other methods in detail for doing what you need http://www.sommarskog.se/arrays-in-sql.html

score 1 · Answer 2 · answered Oct 11 '13 at 22:44

1

You can use a udf that parses the string and inserts the values into a table (containing one int column in your case), and then join your Curso table on the table resulting from calling the udf on your CSV string.

answered Oct 11 '13 at 22:44

mayabelle

9,804
9
36
59

score 0 · Answer 3 · answered Oct 11 '13 at 23:18

0

I would pass it as xml. Its easy and very performant. Here is a good post with the details. https://www.simple-talk.com/blogs/2012/01/05/using-xml-to-pass-lists-as-parameters-in-sql-server/

answered Oct 11 '13 at 23:18

jcwrequests

1,132
1
7
13

T-SQL: pass an array of ids to a stored procedures

3 Answers3