CODEWITHSUNDEEP
sql-serverentity-frameworkstored-procedures
Michael
Michael

Reputation: 13626

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

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?

Upvotes: 1

Views: 7975

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81990

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',',')

Upvotes: 1

marc_s
marc_s

Reputation: 754938

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)

Upvotes: 2

Related Questions