Send array parameters to SQL Server

 

-- Use temporary tables for splitting

Declare @ s varchar (4000)
Set @ s = '-1;-1; 0; 0; 0 ;'

Select top 8000 id = identity (INT,) into # From syscolumns A, syscolumns B
Select substring (@ s, ID, charindex (';', @ s + ';', ID)-ID)
From #
Where substring (';' + @ s, ID, 1) = ';'
And id <Len (@ s)
Drop table #

/*
Function: functions that implement the split Function
*/

Create Function fn_split
(
@ Inputstr varchar (8000 ),
@ Seprator varchar (10)
)
Returns @ temp table (A varchar (200 ))
As

Begin
Declare @ I int

Set @ inputstr = rtrim (ltrim (@ inputstr ))
Set @ I = charindex (@ seprator, @ inputstr)

While @ I> = 1
Begin
Insert @ temp values (left (@ inputstr, @ I-1 ))

Set @ inputstr = substring (@ inputstr, @ I + 1, Len (@ inputstr)-@ I)
Set @ I = charindex (@ seprator, @ inputstr)
End

If @ inputstr <> '/'
Insert @ temp values (@ inputstr)

Return
End
Go


-- Call
Select * From DBO. fn_split ('-1;-1; 0; 0; 0 ;',';')

-- Result
/*

-1
-1
0
0
0


*/