Passing an 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

SQL Server 2000 Forums

Transact-SQL (2000)

Passing an array to a stored procedure

Author

Topic

reksten
Starting Member

2 Posts

Posted - 2002-10-03 : 10:04:43

Does anyone know how to pass an array to a stored procedure?

The stored procedure I'm having difficulties with creates a temporary table, so that the resultset can be paged to an ASP page.

Here is the important part of the stored procedure simplified (I removed all the joins, etc., the actual SELECT is much, much longer. The column names and table name have been changed to protect the innocent :)

INSERT INTO #TempItems (PNum, AName)
SELECT table.PNum, table.AName FROM table WHERE table.PNum = @Param

[**NOTE WHERE @Param would be an array??, so that the query (expanded) would look something like this:

'6237364' OR table.PNum = '5109062' OR table.PNum = '5124497' ... etc, etc.**]

Thanks,

Robert Eksten

Edited by - reksten on 10/03/2002 10:18:44

Edited by - reksten on 10/03/2002 10:20:15

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-03 : 10:12:38

Parsing CSV Values Into Multiple Rows

..and read the comments thread associated with the article

Jay White
{0}

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-03 : 10:13:22

You can pass multiple elements as a comma-separated value (CSV) and get the functionality of an array. Read these:

http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv

There are several techniques available in those articles that will get you started.

Edited by - robvolk on 10/03/2002 10:13:59

reksten
Starting Member

2 Posts

Posted - 2002-10-03 : 22:27:01

Thanks for pointing me to some good resources.

I am banging my head against the wall here. I read and followed the advice you gave me, but I'm still having problems, and I'm afraid I'm not smart enough with tsql to figure this one out.

Any help would be tremendously appreciated before I loose my mind!

ASP CODE:

<%
REM FINAL SQL QUERY
Set adoRsDisplay = Server.CreateObject("ADODB.Recordset")

Dim currentPage
Dim iRecordsPerPage

currentPage = 1
iRecordsPerPage = 15

strSP = "sp_Page " & currentPage & "," & iRecordsPerPage & "," & strResultList_FINAL & ",'$'"

adoRsDisplay.Open strSP, adoCn

adoRsDisplayCount = adoRsDisplay("TotalRecords")
%>

**strResultList_FINAL = $US 6013851$US 6013727$US 6013598$US 6013594$US 6013353$US 6013239$US 6013176$US 6013171$

CREATE PROCEDURE dbo.sp_Page
(
@Page int,
@RecsPerPage int,
@Param varchar(8000),
@Separator char(1)
)
AS

SET NOCOUNT ON

DECLARE @RecCount int
SELECT @RecCount = @RecsPerPage * @Page + 1
CREATE TABLE #TempItems
(
ID int IDENTITY,
PatentNumber varchar(30),
ORClasscode varchar(12),
AssigneeName varchar(4096),
FullTitle varchar(2048),
IPCCode varchar(14),
PubDate char(8)
)

DECLARE @Separator_position int
DECLARE @Param_value varchar(30)

WHILE patindex('%' + @Separator + '%' , @Param) <> 0
BEGIN

SELECT @Separator_position = patindex('%' + @Separator + '%' , @Param)
SELECT @Param_value = left(@Param, @Separator_position - 1)

INSERT INTO #TempItems (PatentNumber, ORClasscode, AssigneeName, FullTitle, IPCCode, PubDate)
SELECT pps_DEC020.PatentNumber, pps_DEC020.ORClasscode, pps_DEC032.AssigneeName, pps_DEC035.FullTitle, pps_DEC040.IPCCode, pps_DEC041.PubDate FROM pps_DEC020 INNER JOIN pps_DEC035 ON pps_DEC020.PatentNumber = pps_DEC035.PatentNumber INNER JOIN pps_DEC032 ON pps_DEC020.PatentNumber = pps_DEC032.PatentNumber INNER JOIN pps_DEC040 ON pps_DEC020.PatentNumber = pps_DEC040.PatentNumber INNER JOIN pps_DEC041 ON pps_DEC020.PatentNumber = pps_DEC041.PatentNumber WHERE pps_DEC020.PatentNumber = @Param_value

END

DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID > @LastRec
) ,
TotalRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec
SET NOCOUNT OFF
GO

!#&%$ ASP ERROR:

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near 'US'.
/p_EXMO/includes/i_displayHandlerS4.asp, line 13

LINE 13:
adoRsDisplay.Open strSP, adoCn

Thanks,

Robert Eksten

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources