Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    1

    Unanswered: How to pass multiple values to an "IN" clause through stored procedure

    I created a stored procedure like the following in the hope that I can pass mulitple company_id to the select statement:


    CREATE PROC sp_test @in_company_code nvarchar(1024)
    AS

    select company_code, name, description
    from member_company
    where company_code in (@in_company_code)


    However, I tried the following :

    exec sp_test 'abc', 'rrd', 'bbc'

    Procedure or function sp_test has too many arguments specified.

    and SQLServer doesn't like it.

    Did I specify this stored procedure correct?
    If so, how can I can pass multiple values to the stored procedure then to the sql statement?
    If not, is it possible to specify a stored procedure like this?

    Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sure

    check this out

    http://www.sqlteam.com/forums/topic....udf,csv,string

    or wait for yukon
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    This is probably the same stuff you'd get from that link...but I ran across this last week, and was helped greatly by others on this site and others...

    I eventually wound up with a function like this:
    Code:
    CREATE FUNCTION [dbo].[fn_CSVList_FilteredPortfolioTable] (@CsvList varchar(4000))
    RETURNS table 
    AS
    
    RETURN (  SELECT TOP 100 PERCENT *
    	      FROM Portfolio (nolock)
    	      WHERE (CHARINDEX(',' + CAST(PortfolioID AS VARCHAR) + ',', ',' + @CsvList + ',') > 0)
    	      ORDER BY PortfolioID)
    This function takes a CSV string as input ('abc, rrd, bbc' in your example) and just returns a row from the table for each entry in the list.

    I think you could modify it to fit your needs fairly easily.

    I also have some code that takes individual values from a table/select and creats the CSV list used as input for this function if your CSV string (aka list of companies) is variable (this also primarily provided by others on this and other lists).
    Last edited by TallCowboy0614; 06-11-04 at 17:15.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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