Passing array to stored procedure

Can some one tell me the way to pass Table or Array or list of values to stored procedure?

[Note: I already know some ways which are not useful in my case:

- By passing a list of values separting each with a delimeter like comma(,)

- Using XML

]

Any other way?

Please, its urgent

Thanks in advance

http://www.google.de/search?hl=de&q=array+stored+procedure&meta=

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens...

The reference sites you provided mentioned things I already tried...

Please can you show me other ways to do it...

Cause my project has bulky transactions.... So these methods won't work here....

I need to pass Entire Table or Array from C# (Front end) to stored procedure...

and also multiple user (more than 100 concurrent users) access same resources at the same time...So I cannot use XML (file systems) for this task.

I need to pass these data in other ways...

Please help me out...

|||

How about creating a table in the database which has two fields (userId, value), assuming that the lowest granularity of the concurrence is at user level, and filling it with all the data you want to pass and call the stored procedure with userId as a parameter.

The stored procedure reads the values from the table where userId = @.userId and deletes the values at the end to keep the table clean.

|||A simple row can be passed using a comma delimited string and splitting this on the server to the appriopate values. For more than one row you would have to use a more sophisticated funtionality. The lat option would be to use something like a materialized table, as the previous poster already mentioned.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks Jens and ,

I tried as you said, I created a class in C# which creates a global temporary table in SQL Server, then I executed Stored procedure which manipulates that temporary table, and finally when stored procedure does its work, this class deletes the temporary table.

To avoid conflicts in session and user from using same table, I suffixed the table with date time when the table was created....

This table is dynamic, it can be created by passing DataTable from C#....

It solved my problem....

Thanks both of you for giving me idea....

|||

Hi,

Jens and Lakshmana

The Idea you guys gave me helped me a lot.

I created a class (in C#) that first creates a global temporary table in SQL Server, then executes stored procedure. This procedure

manipulates the temporary table and takes data stored in this table. When stored procedure finishes its task, the class in C#, deletes the temporary table.

To avoid conflict in sessions and multiple users from using same table, I suffixed the table with date time when the table is being created...

This helped me a lot (though it is a negative from performance point of view)...

Thank you guys....

|||Hi All|||

Hi All

thnx for ur comments

really its all Usefull

i Just Have small Problem .

after i Created the global temporary table in SQL Server , and Inserted the Data i wanna use in the Stored Procedure

i Pass the temporary table Name to the Stored Procedure

But i couldnt use it with aCursor or even Normal Select Statement.

coz i have to concatinate it in the select Statemant

Searchin For Help

Thanks for all of u

|||Hi All.
This is one problem that kept on following me. I tried all the other suggestions and it worked fine at the time. Recently I decided to look for an alternative that will give me more flexibility.
My problem was I need to pass a list of keys to a stored proc (sp). The system I work on was architectured to work over slow networks and the internet, so no unnecessary call to the backend, hence my need to pass a list of keys.

I pass my keys in as a delimeted string (delimeter can be "," or "|" or any chosen character, this will be more clear later).
On the database I have a table-value function that convert this delimeted string to a table and this allows me to use it in joins and "where fk_somekey in (select * strval from delimtable)" statements.
Here is the function:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Lucian@.Probia.co.za
-- Create date: 2006/11/22
-- Description: Returns table for delimited string
-- =============================================
Create FUNCTION [dbo].[DelimStrToTable]
(
-- Add the parameters for the function here
@.delimStr varchar(8000),
@.delimchar char
)
RETURNS
@.StrValTable TABLE
(
-- Add the column definitions for the TABLE variable here
StrVal varchar(1000)

)
AS
BEGIN
-- Fill the table variable with the rows for your result set
declare @.strlist varchar(8000), @.pos int, @.delim char, @.lstr varchar(1000)
set @.strlist = @.delimStr
set @.delim = @.delimchar

while ((len(@.strlist) > 0) and (@.strlist <> ''))
begin
set @.pos = charindex(@.delim, @.strlist)

if @.pos > 0
begin
set @.lstr = substring(@.strlist, 1, @.pos-1)
set @.strlist = ltrim(substring(@.strlist,charindex(@.delim, @.strlist)+1, 8000))
end
else
begin
set @.lstr = @.strlist
set @.strlist = ''
end
Insert @.StrValTable values (@.lstr)
--print @.lstr
end

RETURN
END

and now I can do this:

Select * from Authors where AuthorId in (select convert(int,strval) from DelimStrToTable('1|2|11|45', '|'))

or
Select A.* from Authors A inner join DelimStrToTable('1|2|11|45', '|') as IDLst
on A.AuthorId = convert(int,IDLst.strval)

One is of course limited by the size of stored proc parameter. For most of my scenarios, this worked fine. Hope it serves as an alternative.

Lucian|||I think the line
StrVal varchar(1000)
can be changed to
StrVal varchar(10)
The size here depend on the size of your list item in the parameter. Size of 10 is fine for a list of integers.
sql