How do I declare array of integers in a SQL Server stored procedure?

Question

I use SQL Server 2012.

I have this int[] array:

int[] arr = [1,23,4,3];

arr variable I sent as parameter to stored procedure.

How do I declare this variable? How do I declare array of integers in a SQL Server stored procedure?

Stored procedure code is **highly** vendor-specific - therefore please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Oct 23 '16 at 18:00
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) — Cee McSharpface, Oct 23 '16 at 18:05

score 2 · Accepted Answer · answered Oct 23 '16 at 18:06

2

SQL Server doesn't have any "array" type - if you need multiple values of something, there's really only one construct : a table.

If you need to pass multiple values to a stored procedure, you should check out table-valued parameters (TVP). Those allow you to send in a "table variable" full of values.

Same applies inside your stored procedure - if you need to handle multiple values, use a table variable (@MyTable) or a "regular" temporary table (#MyTempTable)

answered Oct 23 '16 at 18:06

marc_s

732,580
175
1,330
1,459

Can I use TVP inside query to filter data? SELECT * FROM dbo.Sites WHERE dbo.Clients.Id IN TVP – Michael Oct 23 '16 at 18:20
@Michael: yes, of course - it's like a normal table variable inside your procedure! – marc_s Oct 23 '16 at 18:42

John Cappelletti · Answer 2 · 2016-10-23T20:18:36.623

1

As mentioned above, SQL Server does not support arrays, however, any Split/Parse function will do

EDIT To Include in a WHERE

Declare @String varchar(max) ='1,23,4,3'

Select * 
 From  Clients 
 Where Clients.Id IN ( Select RetVal from [dbo].[udf-Str-Parse](@String,','))

The UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')

edited Oct 23 '16 at 20:18

answered Oct 23 '16 at 18:18

John Cappelletti

79,615
7
44
66

my purpose is to sent array of integers to stored procedure and then to use this array in where statment in the query. – Michael Oct 23 '16 at 18:32
@Michael Figured as much. I often do the same, the results can easily be incorporated into your WHERE or even a JOIN – John Cappelletti Oct 23 '16 at 18:48
@Michael Say your second post. Consider the updated anwer – John Cappelletti Oct 23 '16 at 20:19

How do I declare array of integers in a SQL Server stored procedure?

2 Answers2

Linked