Passing an array into 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 into a stored procedure

Author

Topic

tegryan
Starting Member

22 Posts

Posted - 2002-04-15 : 13:24:03

Hello all.
I am trying to run a select statement from a stored procedure where the "where" clause can contain one or more conditions, depending on what kind of arguments are passed in from the ASP page. Basically, I am summing up some records for a rep code which is based on the username/password supplied, but some user's have more than one rep code, so when they log in, i need the procedure to do the summation on a couple of rep codes, not just one. for example, here is the original (stripped down) stored procedure for just one rep code:

CREATE PROCEDURE sp_SelectSummation

@RepCode varchar(30)

As

SELECT [summation] FROM TABLE

WHERE (RepCode = @RepCode)

what i need is for that to be able to handle input where there more than one repcode. I have passed the repcode value in as CSV value, and run a REPLACE command on it, creating a variable (@RepCode) that contains this: "repcode1 or (repcode = repcode2) or (repcode = repcode3)" hoping that the SP would be able to concatonate the where statement before it runs it and turn it into a statement that SQL server can understand, but apparantly it doesnt. I'm pretty sure this is the wrong approach, but I can't think of any other way to do it, except to run the same stored procedure once for each rep code, and then sum the totals, which is doubling, tripling, quadrupling... the workload on the server. anyone have any ideas at all? i'd really appreciate it!

skond
Yak Posting Veteran

55 Posts

Posted - 2002-04-15 : 14:59:04

try this ...

set quoted_identifier off
go
CREATE PROCEDURE sp_SelectSummation
@RepCode varchar(500) = 'abc,cde,efg' -- should be replaced with actual rep codes seperated with comma.
As

set @RepCode = "'" + replace(@RepCode,',',"','") + "'"

SELECT [summation] FROM TABLE
WHERE RepCode in (@RepCode)

skond
Yak Posting Veteran

55 Posts

Posted - 2002-04-15 : 15:10:05

My last response wouldn't work. Try this...

set quoted_identifier off
go
CREATE PROCEDURE sp_SelectSummation
@RepCode varchar(500) = 'abc,cde,efg' -- should be replaced with actual rep codes seperated with comma.
As
declare @sql varchar(8000)

set @RepCode = "'" + replace(@RepCode,',',"','") + "'"

set @sql = '
SELECT [summation] FROM TABLE
WHERE RepCode in (' + @RepCode + ')'

exec (@sql)

tegryan
Starting Member

22 Posts

Posted - 2002-04-15 : 15:38:50

I'm pretty sure that this:

set @sql = '
SELECT [summation] FROM TABLE
WHERE RepCode in (' + @RepCode + ')'

exec (@sql)

Was the missing piece. I couldn't figure out how to excute the sql statement that has a stored procedure variable in the where clause. I'll try it after lunch. Thanks much.

bm1000
Starting Member

37 Posts

Posted - 2002-04-15 : 15:58:41

I would use something like the following. It does not use dynamic sql and can handle a variable number of parameters.

create procedure sp_getauthors (
@au_id1 varchar(12)=null
,@au_id2 varchar(12)=null
,@au_id3 varchar(12)=null)
as

SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract
FROM pubs.dbo.authors
where au_id in (@au_id1,@au_id2,@au_id3)

To execute:

execute sp_getauthors @au_id1 = '172-32-1176',@au_id2='238-95-7766'

tegryan
Starting Member

22 Posts

Posted - 2002-04-15 : 16:02:42

will that work if i don't know how many parameters (rep codes in this case) are being passed in tho? the crux of the problem is that it might be only one, or 50 in the future. thanks for your reply too.

digory
Starting Member

13 Posts

Posted - 2002-04-15 : 16:14:34

You might also like to check out this function:

[url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14185[/url]

Which allows you to pass an array - using any delimiter - into a sproc.

bm1000
Starting Member

37 Posts

Posted - 2002-04-15 : 19:28:15

It will work with different numbers of parameters, but only up to the number of parameters defined. The sp can accept up to three parameters, but in the sample statement I passed only two.

If you really need to handle 50 parameters, dynamically building the statement may be the better solution.

quote:

will that work if i don't know how many parameters (rep codes in this case) are being passed in tho? the crux of the problem is that it might be only one, or 50 in the future. thanks for your reply too.

tegryan
Starting Member

22 Posts

Posted - 2002-04-15 : 19:54:23

yeah, bm1000, that was the original idea I thought of, but I don't want to have the system crash a year down the road because there is suddenly more parameters than i origanlly planned for. i'm really not sure of how many there will be in the end.

anywho, skond, i can't get your idea to work, because SQL server isn't executing the sql statement as a sql statement, i think. I could be totally wrong. here is the stored procedure and the error message, i can't figure out why it's doing that.

<<Edited code>>

And this is the error I get, i have never seen this one before, i'm going to search around for clues, but if anyone knows the answer, i'd appreciate the help!

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value ' Select sum(ExeShares) as ExeShares, sum(Coms) as Coms, count(OrderNo) as NumberOfTrades, sum(CommissionableTickets) as CommissionableTickets From Trades WHERE ([Date] between ' to a column of data type int.

Edited by - tegryan on 08/30/2002 12:53:19

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-04-15 : 20:48:20

A quick search on CSV (http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv) will give you a few articles that parse a CSV into rows. You can join that to you target table. Easier is probably dynamic SQL (http://www.sqlteam.com/SearchResults.asp?SearchTerms=dynamic+sql).

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

tegryan
Starting Member

22 Posts

Posted - 2002-04-16 : 11:28:37

thanks very much to all who had input for this. If anyone has this situation, and wants to know how to solve it, I finally fixed it by using the teqniques in this article: http://www.sqlteam.com/item.asp?ItemID=4619

thanks again all!

skond
Yak Posting Veteran

55 Posts

Posted - 2002-04-16 : 15:52:03

-- Make sure you add the first two lines as shown below.
-- @FromDate and @ToDate should be defined as VARCHAR, Integer cannot be converted implicitly to Datetime.

SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE sp_SelectSumExeSharesComs2
@RepCode varchar(500),
@FromDate varchar(10),
@ToDate varchar(10)

As

declare @sql varchar(8000)
declare @ADP char(3)
set @ADP = 'ADP'

set @RepCode = "'" + replace(@RepCode,',',"','") + "'"

set @sql = "Select sum(ExeShares) as ExeShares, sum(Coms) as Coms, count(OrderNo) as NumberOfTrades, sum(CommissionableTickets) as CommissionableTickets From Trades
WHERE ([Date] between '" + @FromDate + "' and '" + @ToDate + "') and LTrim(Route) <> '" + @ADP + "' and (RepCode in (" + @RepCode + "))"

exec (@sql)

tegryan
Starting Member

22 Posts

Posted - 2002-04-17 : 14:12:54

That's exactly what it was skond. thankyou, come to winnipeg and i'll buy you a beer!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-04-22 : 10:50:00

Passing a list of parameters to a stored procedure can be done by putting them into a string with a separator. I like to use the traditional comma. Let's assume that you have a whole table full of such parameter lists:

CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);

INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
...

This will be the table that gets the outputs, in the form of the original key column and one parameter per row.

CREATE TABLE Parmlist
(keycol CHAR(5) NOT NULL PRIMARY KEY,
parm INTEGER NOT NULL);

It makes life easier if the lists in the input strings start and end with a comma. You will also need a table called Sequence, which is a set of integers from 1 to (n).

SELECT keycol,
CAST (SUBSTRING (',' + I1.input_string + ',', MAX(S1.seq + 1),
(S2.seq - MAX(S1.seq + 1)))
AS INTEGER),
COUNT(S2.seq) AS place
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' + I1.input_string + ',' , S1.seq, 1) = ','
AND SUBSTRING (',' + I1.input_string + ',' , S2.seq, 1) = ','
AND S1.seq < S2.seq
AND S2.seq <= DATALENGTH(I1.input_string) + 1
GROUP BY I1.keycol, I1.input_string, S2.seq
ORDER BY I1.keycol, I1.input_string, S2.seq

The S1 and S2 copies of Sequence are used to locate bracketing pairs of commas, and the entire set of substrings located between them is extracts and cast as integers in one non-procedural step. The trick is to be sure that the left hand comma of the bracketing pair is the closest one to the second comma. The place column tells you the relative position of the value in the input string

--CELKO--
Joe Celko, SQL Guru

carlmalden
Starting Member

1 Post

Posted - 2008-07-25 : 16:42:43

quote:
Originally posted by digory

You might also like to check out this function:

[url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14185[/url]

Which allows you to pass an array - using any delimiter - into a sproc.

If you want to use this function with SQL2005...

Here it is:

---------------------- ArrayToTable.sql -----------------

CREATE FUNCTION ArrayToTable ( @array VarChar( 4000 ), @delimiter VarChar( 6 ) )

/*
Name: ArrayToTable
Return Values: TABLE ( ( VarChar ) Fld )
Description: Receives a delimited list of values and returns them as
a single column table of varchar values
*/

RETURNS @tblReturn TABLE (
Fld VarChar( 500 )
) AS
BEGIN
-- track where in the text we are...
DECLARE @startPos Int
DECLARE @endPos Int

-- each element that we find in the text
DECLARE @elementValue VarChar( 500 )

SET @array = @array + @delimiter
SET @startPos = 0
SET @endPos = CHARINDEX( @delimiter, @array, @startPos )

-- loop until we reach the end of the text
WHILE ( ( @endPos > 0 ) And ( @startPos < LEN( @array ) ) )
BEGIN
SET @elementValue = CONVERT( VarChar( 500 ), LTRIM( RTRIM( SUBSTRING( @array, @startPos, @endPos - @startPos ) ) ) )
-- did we find a valid element?
IF( LEN( @elementValue ) > 0 )
INSERT INTO @tblReturn ( [Fld] ) VALUES ( @elementValue )
-- move the pointers
SET @startPos = @endPos + LEN( @delimiter )
SET @endPos = CHARINDEX( @delimiter, @array, @startPos )

END

RETURN

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-26 : 14:07:21

quote:
Originally posted by skond

My last response wouldn't work. Try this...

set quoted_identifier off
go
CREATE PROCEDURE sp_SelectSummation
@RepCode varchar(500) = 'abc,cde,efg' -- should be replaced with actual rep codes seperated with comma.
As
declare @sql varchar(8000)

set @RepCode = "'" + replace(@RepCode,',',"','") + "'"

set @sql = '
SELECT [summation] FROM TABLE
WHERE RepCode in (' + @RepCode + ')'

exec (@sql)

you could even do this as follows

CREATE PROCEDURE sp_SelectSummation 
@RepCode varchar(500) = 'abc,cde,efg' -- should be replaced with actual rep codes seperated with comma. 
As 

SELECT [summation] FROM TABLE 
WHERE ',' + @RepCode + ',' LIKE '%,'RepCode +',%'

make sure to cast RepCode to varchar if its of non character type

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources