Providing an Array as a stored procedure parameter

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

SQL Server 2000 Forums

SQL Server Development (2000)

Providing an Array as a stored procedure parameter

Author

Topic

jimdummett
Starting Member

2 Posts

Posted - 2003-08-11 : 10:41:39

hi.

i've been struggling with a problem on SQL 7 and 2000 for a while now- i wonder if anyone has any ideas of a solution?

i've developed a webmail application using ASP that uses an SQL Server database to store all the mails in and interfaces with the database via stored procedures.

the problem arises when the stored procedures need to be provided with a list of parameters, the length of which is unknown and unbounded. for example, the user is presented with a list of all the mails in their inbox and can select one or more mails for deletion. the array of IDs of the mails to be deleted needs to be provided as a parameter to the stored procedure.

the solution i am using at present is for the ASP page to build an SQL UNION statement giving the IDs of the mails and to pass this as a string to the stored procedure. for example, if the user selects mails 101, 202 and 505 to be deleted, the ASP page builds the string 'SELECT 101 UNION ALL SELECT 202 UNION ALL SELECT 505' and passes it the the stored procedure. the stored procedure then runs that string as a piece of dynamic SQL and inserts the rows into a temporary table. the stored procedure can use the values in this temporary table to join to other tables, delete rows etc etc.

the stored procedure looks something like this:

CREATE PROC spDeleteMails
     @MailIDsString NTEXT
AS

CREATE TABLE #DeleteMails (ID INT NOT NULL)

INSERT INTO #DeleteMails (ID)
EXEC (@MailIDsString)

DELETE tblMail
FROM #DeleteMails
WHERE tblMail.ID = #DeleteMails.ID

DROP TABLE #DeleteMails

now that i'm using SQL 2000, i would like to replace the temporary table #DeleteMails with a table variable to save on usage of the tempdb database (and to avoid the other inefficiencies of using a temporary table). unfortunately table variables don't accept the 'INSERT INTO @TableVar (...) EXECUTE ...' form of statement so a simple switch is not possible.

does anyone have any ideas how i can get round this problem? or any better approaches to providing stored procedures with an array of values as an input? i would have thought this type of problem would be a fairly common one, but i have never seen a discussion of it in any SQL books or on the web.

thanks for reading. hope you can help...

jim dummett

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-11 : 10:54:04

Passing a CSV or Array to a Stored Procedure
Parsing CSV Values Into Multiple Rows

Jay White
{0}

jimdummett
Starting Member

2 Posts

Posted - 2003-08-11 : 11:02:47

thanks jay.

i've tried this approach too before, but found that when the length of the array gets long, the parsing gets very slow. is it significantly better when using a table variable rather than a temp table?

cheers,

jim

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-11 : 12:48:01

quote:
is it significantly better when using a table variable rather than a temp table?

Not if you are using SQL Server 7

.

I've had good success with the tally table method for parsing the csv. See my comments in reply to the article ...

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources