SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Passing an array into a stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tegryan
Starting Member

22 Posts

Posted - 04/15/2002 :  13:24:03  Show Profile  Send tegryan a Yahoo! Message  Reply with Quote
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

USA
55 Posts

Posted - 04/15/2002 :  14:59:04  Show Profile  Reply with Quote
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)

Go to Top of Page

skond
Yak Posting Veteran

USA
55 Posts

Posted - 04/15/2002 :  15:10:05  Show Profile  Reply with Quote
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)


Go to Top of Page

tegryan
Starting Member

22 Posts

Posted - 04/15/2002 :  15:38:50  Show Profile  Send tegryan a Yahoo! Message  Reply with Quote
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.

Go to Top of Page

bm1000
Starting Member

37 Posts

Posted - 04/15/2002 :  15:58:41  Show Profile  Reply with Quote
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'


Go to Top of Page

tegryan
Starting Member

22 Posts

Posted - 04/15/2002 :  16:02:42  Show Profile  Send tegryan a Yahoo! Message  Reply with 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.

Go to Top of Page

digory
Starting Member

Australia
13 Posts

Posted - 04/15/2002 :  16:14:34  Show Profile  Visit digory's Homepage  Reply with Quote
You might also like to check out this function:

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

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


Go to Top of Page

bm1000
Starting Member

37 Posts

Posted - 04/15/2002 :  19:28:15  Show Profile  Reply with Quote
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.





Go to Top of Page

tegryan
Starting Member

22 Posts

Posted - 04/15/2002 :  19:54:23  Show Profile  Send tegryan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 04/15/2002 :  20:48:20  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page

tegryan
Starting Member

22 Posts

Posted - 04/16/2002 :  11:28:37  Show Profile  Send tegryan a Yahoo! Message  Reply with Quote
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!

Go to Top of Page

skond
Yak Posting Veteran

USA
55 Posts

Posted - 04/16/2002 :  15:52:03  Show Profile  Reply with Quote
-- 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)




Go to Top of Page

tegryan
Starting Member

22 Posts

Posted - 04/17/2002 :  14:12:54  Show Profile  Send tegryan a Yahoo! Message  Reply with Quote
That's exactly what it was skond. thankyou, come to winnipeg and i'll buy you a beer!

Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 04/22/2002 :  10:50:00  Show Profile  Visit jcelko's Homepage  Reply with Quote
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
Go to Top of Page

carlmalden
Starting Member

1 Posts

Posted - 07/25/2008 :  16:42:43  Show Profile  Reply with Quote
quote:
Originally posted by digory

You might also like to check out this function:

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

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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/26/2008 :  14:07:21  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000