SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Passing a CSV or Array to a Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/08/2000 :  12:29:16  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Alex writes "I have a need to run a stored procedure which will insert multiple rows in the database. Biggest problem that I don't know from the begging how much inserts I need to do. I need to pass in the stored procedure an array of variables and loop over it. How I can pass complex datasets like arrays,lists, etc. in the stored procedure? How I can loop over that datasets in the stored procedure?" I've gotten this question a couple of times and here's how to do it.

Article Link.

JohnDeere
Posting Yak Master

USA
191 Posts

Posted - 07/25/2001 :  10:28:14  Show Profile  Reply with Quote
The article Treat Yourself to FN_Split() (SQL Server Magazine July 2001 )describes how to build a UDF (SQL 2000) that will let you use a variable length parameter array in T_SQL

LHarra
Go to Top of Page

simon.train@ecatenate.com
Starting Member

2 Posts

Posted - 11/19/2001 :  10:17:45  Show Profile  Reply with Quote
quote:

Firstly, the problem with passing comma separated varchars is that you're limited in length to what you can pass. You can get round this using SQL Server 2000's XML features to pass _unlimited_ length arrays.

Here's how to do it for VB->ADO->SQL Stored Proc:

1. Assuming you have a (string) array of longs in VB use a function to build a simple xml-based listing of your items:

Function ArrayToXML(arrFrom() As String) As String
Dim i As Integer
Dim strTemp As String
Dim strElement As String

strTemp = "<A>"
For i = 0 To UBound(arrFrom)
strElement = arrFrom(i)
strTemp = strTemp & "<E I=""" & strElement & """/>"
Next
strTemp = strTemp & "</A>"
ArrayToXML = strTemp
End Function

2. Now pass that into your stored proc using 'text' datatype:
.Parameters.Append .CreateParameter("@XMLArray", adVarChar, adParamInput, 2147483647, ArrayToXML(arrMyIDs))

3. Declare xml doc in your stored procedure:

DECLARE @XMLArrayDoc int
EXEC sp_xml_preparedocument @XMLArrayDoc output, @XMLArray

4. Return the values using FOR OPENXML:

SELECT I As myData FROM OPENXML (@XMLArrayDoc, '/A/E/@I', 1) WITH (I int '.')

You can put these values into a temp table, a cursor or simply use it in an 'IN'. Works with multi-dimensional arrays too.

5. Don't forget to kill the xml doc at the end of your stored proc

EXEC sp_xml_removedocument @XMLArrayDoc



Edited by - simon.train@ecatenate.com on 11/19/2001 10:30:35

Edited by - simon.train@ecatenate.com on 11/19/2001 10:34:02
Go to Top of Page

bart
Starting Member

1 Posts

Posted - 07/10/2002 :  05:52:20  Show Profile  Reply with Quote


alter procedure web_ParseArray
( @Array varchar(1000),
@separator char(1) )
AS
-- Created by graz@sqlteam.com
-- Modified to result a table by Bart@prove.be

set nocount on
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned

create table #ParsedArrays (array_Value varchar(1000))

-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @array = @array + @separator

-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)

-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
insert #ParsedArrays VALUES (@array_value)


-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')
end

set nocount off
select * from #ParsedArrays
drop table #ParsedArrays
go




Go to Top of Page

vmantia
Starting Member

USA
1 Posts

Posted - 12/16/2003 :  16:09:37  Show Profile  Visit vmantia's Homepage  Send vmantia an AOL message  Send vmantia an ICQ Message  Send vmantia a Yahoo! Message  Reply with Quote
can some1 help me out here real quick, i am using this example that you posted to fetch various item numbers, the string i am passing to sql is: 170748,170751,
thing is i only get the first value 170748, can some1 please tell me how to get the rest of my values or what i have to edit in order to do so, sorry for the newbie question :-
CREATE PROCEDURE vm_rscomissbyitemTEST

@DateRange1a varchar(20), @DateRange1b varchar(20), @StartRoute varchar(3), @EndRoute varchar(3),
@Array varchar(1000), @separator char(1)

AS

DECLARE @Date1a smalldatetime, @Date1b smalldatetime

SELECT @Date1a = CONVERT(smalldatetime,@DateRange1a,101)

SELECT @Date1b = CONVERT(smalldatetime,@DateRange1b,101)

set nocount on

declare @separator_position int
declare @array_value varchar(1000)

create table #ParsedArrays (array_value varchar(1000))

set @array = @array + @separator

while patindex('%' + @separator + '%' , @array) <> 0
begin

select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)

select aritrs.citemno, icitem.cdescript, icitem.cmeasure, icitem.cclass, comisc.cdescript as itemclass, arinvc.dinvoice, aritrs.nshipqty,
aritrs.nsalesamt, icitem.ccommiss, aritrs.ndiscamt, arinvc.croute, scrout.cname
from arinvc
left outer join aritrs on aritrs.ccustno = arinvc.ccustno and aritrs.cinvno = arinvc.cinvno
left outer join icitem on icitem.citemno = aritrs.citemno
left outer join comisc on comisc.ccode = icitem.cclass
left outer join scrout on scrout.croute = arinvc.croute
WHERE aritrs.citemno IN (@array_value) AND
scrout.croute BETWEEN @StartRoute AND @EndRoute AND
arinvc.dinvoice BETWEEN @Date1a AND @Date1b
union all
select aritrsh.citemno, icitem.cdescript, icitem.cmeasure, icitem.cclass, comisc.cdescript as itemclass, arinvch.dinvoice, aritrsh.nshipqty,
aritrsh.nsalesamt, icitem.ccommiss, aritrsh.ndiscamt, arinvch.croute, scrout.cname
from arinvch
left outer join aritrsh on aritrsh.ccustno = arinvch.ccustno and aritrsh.cinvno = arinvch.cinvno
left outer join icitem on icitem.citemno = aritrsh.citemno
left outer join comisc on comisc.ccode = icitem.cclass
left outer join scrout on scrout.croute = arinvch.croute
WHERE aritrsh.citemno IN (@array_value) AND
scrout.croute BETWEEN @StartRoute AND @EndRoute AND
arinvch.dinvoice BETWEEN @Date1a AND @Date1b

select @array = stuff(@array, 1, @separator_position, ' ')
end

set nocount off
GO
Go to Top of Page

manofGod
Starting Member

1 Posts

Posted - 06/23/2004 :  17:27:52  Show Profile  Reply with Quote
Modified to result a table with two values.

Create procedure spParseValuePairs
(@Array varchar(1000), @pairseparator char(1),
@rowseparator char(1) )
AS
-- Created by graz@sqlteam.com
-- Modified to result a table by Bart@prove.be
-- Modified to result a table with two values mwashington@atgf.com

set nocount on
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @pairseparator_position int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned
declare @pair_value1 varchar(2)
declare @pair_value2 varchar(2)

declare @ParsedArrays table (array_Value varchar(2), array_Value2 varchar(2))

-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @array = @array + @rowseparator

-- Loop through the string searching for separtor characters
while patindex('%' + @rowseparator + '%' , @array) <> 0
begin
-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @rowseparator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)

-- patindex matches the a pattern against a string
select @pairseparator_position = patindex('%' + @pairseparator + '%' , @array_value)
select @pair_value1 = substring(@array_value, @pairseparator_position - (@pairseparator_position - 1), @pairseparator_position - 1)
select @pair_value2 = substring(@array_value, @pairseparator_position + 1, len(@array_value) - @pairseparator_position)

insert @ParsedArrays VALUES (@pair_value1, @pair_value2)

select @array_value = stuff(@array_value, 1, @pairseparator_position, '')


-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')
end

set nocount off
select * from @ParsedArrays

go

Go to Top of Page

sas8208
Starting Member

1 Posts

Posted - 05/24/2005 :  15:10:26  Show Profile  Reply with Quote
The above code proved to be very helpful. Thanks for posting it!!!
Go to Top of Page

c_swanky
Starting Member

1 Posts

Posted - 07/29/2005 :  18:31:33  Show Profile  Reply with Quote
manofGod's code is VERY helpful.

Any thoughts on how I could extend it to pull more than 2 values?

declare @pair_value3 varchar(2)
declare @pair_value4 varchar(2)
etc...

Thanks
Go to Top of Page

mherman284
Starting Member

USA
1 Posts

Posted - 09/10/2009 :  13:48:10  Show Profile  Reply with Quote
create function fn_ParseArray
/* http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6134 */
(@Array varchar(1000),@separator char(1))
RETURNS @tempTable TABLE (array_Value varchar(1000))
AS
BEGIN
-- Created by graz@sqlteam.com
-- Modified to result a table by Bart@prove.be
-- Modified 9/10/09 to a inline table-value function by mherman@pcgus.com
-- Usage: Select * from tbl join fn_ParseArray ('1,2,3',',') on tbl.id = fn_parsearray.array_Value

-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned

-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @array = @array + @separator

-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)

-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
INSERT INTO @tempTable VALUES (@array_value)

-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')
end
RETURN
END
go
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000