array in store procedure

How to send an array list as an input variable into store procedure?
I have a list of UserName witch I would like to store into table through
store procedure.
HrckoYou can passed it as a string and get the parts out of the string in the SP.
There is no array or such type in TSQL. (AFAIK)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Hrvoje Voda" <hrvoje.voda@.luatech.com> schrieb im Newsbeitrag
news:d4011j$9lf$1@.ss405.t-com.hr...
> How to send an array list as an input variable into store procedure?
> I have a list of UserName witch I would like to store into table through
> store procedure.
> Hrcko
>|||http://www.sommarskog.se/arrays-in-sql.html
Jacco Schalkwijk
SQL Server MVP
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
news:d4011j$9lf$1@.ss405.t-com.hr...
> How to send an array list as an input variable into store procedure?
> I have a list of UserName witch I would like to store into table through
> store procedure.
> Hrcko
>|||Hrcko,
Here is an example that may help. ListTable() is an efficient
table-valued function that can take your list of UserName values
and return a table, so you can do something like
insert into X(UserName)
select Item from ListTable('Hrvoje, Kresimir, Josipa, Barbara, Vladen,
Vilko, Ksenija')
-- Definition and notes for ListTable()
/*
A table-valued function with one parameter, a delimited list,
that returns the separate distinct items of the list.
Steve Kass, Drew University
Thanks to MVPs Linda Wierzbicki and Umachandar Jayachandran
for help and helpful discussions on this.
*/
--A table of integers is needed
create table Seq (
Nbr int not null
)
insert into Seq
select top 4001 0
from Northwind..[Order Details]
cross join (select 1 as n union all select 2) X
declare @.i int
set @.i = -1
update Seq
set @.i = Nbr = @.i + 1
alter table Seq add constraint pk_Seq primary key (Nbr)
--table Seq created
go
--This makes things more readable. The list is easier
--to process if it begins and ends with a single comma
--As it turns out also, list items cannot
--have leading or trailing spaces (here any leading spaces
--in the first item or trailing spaces in the last are
--eliminated)
create function RegularizedList (@.List varchar(8000))
returns varchar(8000) as begin
return replace(rtrim(','+ltrim(@.List))+',', ',,', ',')
end
go
--This function returns a table containing one column, commaPos,
--of integers, the positions of each comma in @.List, except the last
--This function returns a table containing the items in the list.
--The items are extracted by selecting those substrings of
--the list that begin immediately after a comma and end
--immediately before the next comma, then trimming spaces on
--both sides.
create function ListTable (@.List varchar(8000))
returns table as return
select
ltrim(rtrim(
substring(regL,
commaPos+1,
charindex(',', regL, commaPos+1) - (commaPos+1))))
as Item
from (
select Nbr as commaPos
from Seq, (
select dbo.RegularizedList(@.List) as regL
) R
where substring(regL,Nbr,1) = ','
and Nbr < len(regL)
) L, (
select dbo.RegularizedList(@.List) as regL
) R
go
--examples
declare @.x varchar(1000)
set @.x = 'Hrvoje, Kresimir, Josipa, Barbara, Vladen, Vilko, Ksenija'
select * from ListTable(@.x)
set @.x = 'Hrvoje|Kresimir|Josipa|Barbara|Vladen|V
ilko|Ksenija'
declare @.s varchar(1000)
set @.s = replace(@.x,'|',',')
select * from ListTable(@.s)
--Note, if a list contains a non-comma delimiter, and contains no
--commas within items, this replacement allows the function to
--handle it. If a comma appears in an item, but some other non-
--delimiter is absent from the list, a three-step replacement can
--be made:
-- replace all commas with new character not in list
-- replace all delimiters with comma
-- Use (select replace(Item,<new>,<comma> ) from ListTable(@.List)) LT
-- where the list table is used.
go
--Since this is a repro script, delete everything!
--Keep them around if they are helpful, though.
DROP FUNCTION RegularizedList
DROP TABLE Seq
DROP FUNCTION ListTable
-- Steve Kass
-- Drew University
Hrvoje Voda wrote:

>How to send an array list as an input variable into store procedure?
>I have a list of UserName witch I would like to store into table through
>store procedure.
>Hrcko
>
>|||You can pass the list as a comma separated list of values in a string, and
use a split technique to break them apart using a function similar to this
one:
-- Populate an auxiliary table of numbers
SET NOCOUNT ON
USE tempdb
GO
IF OBJECT_ID('Nums') IS NOT NULL
DROP TABLE Nums
GO
CREATE TABLE Nums(n INT NOT NULL PRIMARY KEY)
DECLARE @.max AS INT, @.rc AS INT
SET @.max = 8000
SET @.rc = 1
BEGIN TRAN
INSERT INTO Nums VALUES(1)
WHILE @.rc * 2 <= @.max
BEGIN
INSERT INTO Nums
SELECT n + @.rc FROM Nums
SET @.rc = @.rc * 2
END
INSERT INTO Nums
SELECT n + @.rc FROM Nums
WHERE n + @.rc <= @.max
COMMIT TRAN
GO
-- Create function
CREATE FUNCTION fn_SeparateElements
(@.arr AS VARCHAR(7999)) RETURNS TABLE
AS
RETURN
SELECT n - LEN(REPLACE(LEFT(arr, n), ',', '')) + 1 AS pos,
SUBSTRING(@.arr, n, CHARINDEX(',', @.arr + ',', n) - n) AS element
FROM (SELECT @.arr AS arr) AS A JOIN Nums
ON n <= LEN(@.arr) AND SUBSTRING(',' + @.arr, n, 1) = ','
GO
-- Test
SELECT * FROM fn_SeparateElements('user1,user2,user3')
pos element
-- --
1 user1
2 user2
3 user3
BG, SQL Server MVP
www.SolidQualityLearning.com
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
news:d4011j$9lf$1@.ss405.t-com.hr...
> How to send an array list as an input variable into store procedure?
> I have a list of UserName witch I would like to store into table through
> store procedure.
> Hrcko
>|||
"Hrvoje Voda" wrote:

> How to send an array list as an input variable into store procedure?
>
Definitely more than one way here to skin that cat. :-)
Yet another solution not using a UDF utilizing SQL Server's own
master..spt_values. You can easily modify it to use your own numbers table.
DECLARE @.strComma VARCHAR(1000)
SET @.strComma = 'Hrvoje, Kresimir, Josipa, Barbara, Vladen, Vilko, Ksenija'
SET @.strComma = REPLACE(@.strComma,' ', '')
SELECT
CAST(RIGHT(LEFT(@.strComma,Number-1)
, CHARINDEX(',',REVERSE(LEFT(','+@.strComma
,Number-1)))) AS CHAR(30)) as
User__Name
FROM
master..spt_values
WHERE
Type = 'P' AND Number BETWEEN 1 AND LEN(@.strComma)+1
AND
(SUBSTRING(@.strComma,Number,1) = ',' OR SUBSTRING(@.strComma,Number,1) = '')
User__Name
--
Hrvoje
Kresimir
Josipa
Barbara
Vladen
Vilko
Ksenija
(7 row(s) affected)
--
Frank Kalis
SQL Server MVP
http://www.insidesql.de