Article: Passing a CSV or Array to a Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Passing a CSV or Array to a Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-09-08 : 12:29:16

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.

bart
Starting Member

1 Post

Posted - 2002-07-10 : 05:52:20

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

vmantia
Starting Member

1 Post

Posted - 2003-12-16 : 16:09:37

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

manofGod
Starting Member

1 Post

Posted - 2004-06-23 : 17:27:52

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

sas8208
Starting Member

1 Post

Posted - 2005-05-24 : 15:10:26

The above code proved to be very helpful. Thanks for posting it!!!

c_swanky
Starting Member

1 Post

Posted - 2005-07-29 : 18:31:33

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

mherman284
Starting Member

1 Post

Posted - 2009-09-10 : 13:48:10

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources