Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    1

    Unanswered: 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!

  2. #2
    Join Date
    Jul 2006
    Posts
    87
    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.co...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

  3. #3
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    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

  4. #4
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •