Possible to specifying a list as a parameter to a stored procedure ?

I have been converting a VB 6 applications database queries into SQL Server 2000 stored procedures and have come up against a problem where lists are used in search conditions...For example a list of accounts are selected based on their account currency ID being equal to 1, 5, or 7. In the VB 6 query the string looks like...

SELECT tblAccount.txtName FROM tblAccount WHERE (tblAccount.intCurrencyId IN(1, 5, 7))

The list could contain a single value or upto 20 values. Is it possible to pass the currency list (i.e "1, 5, 7, ...") as a parameter to the stored procedure?

Any help much appreciated!The answer is, "maybe".

It depends on your needs for performance. Please take a look at this discussion for more details.

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=15403

Essentially, if you pass the list as a comma delimited string, then you will either need to parse it inside the query or use it in a dynamic SQL query within the SP. Your other choice is to take all 20 objects as single parameters to your SP. Your IN statement would then be a large set of OR statements for each of the 20 items.

I hope this helps,

CC|||I have to write a lot of stored procedures for reports. I always declare my parameters like

level1 varchar(255)

I then look at the incoming value. I use charindex to find ';' or ',' If I find either I know I have to use "in" in the where clause and format the values correctly.

IF CHARINDEX(';',@.LEVEL1)>0
BEGIN
SET @.LEVEL1=REPLACE('('+''''+REPLACE(@.LEVEL1,';',''''+ ','+'''')+''''+')',' ','')
END

If it is prompt is equal to '%' for all I make my where clause a like, if it is a single value I use equal. The trick to making this so flexible is to use dynamic sql. If you don't know what the parameter will be before hand it seems to be the best way.

' AND ISNULL(T1.DIVISION,'+''''+'NONE'+''''+')' +
case when CHARINDEX(',',@.LEVEL1)>0 then 'in '+@.LEVEL1
else
CASE @.LEVEL1 when '%' THEN ' LIKE '+''''+@.LEVEL1+''''+'+'+''''+'%'+''''
ELSE '='+''''+@.LEVEL1+'''' END
END

Sorry for the formating. It looks better in the actual file|||Oh and I just realized something. If the incoming value is '%' then do not add a condition for it in the dynamic where clause. It makes zero sense to add anything to a where clause if you don't need to.