Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2007
    Posts
    7

    Question Unanswered: Passing a stored procedure parameter into an IN clause

    Hi All

    I have a stored procedure which, initially, I had passed a single parameter into a WHERE clause (e.g ...WHERE CustomerCode = @CustCode). The parameter is passed using a DECommand object in VB6.

    I now require the sp to return values for more than one customer and would like to use an IN clause (e.g ...WHERE CustomerCode IN(@CustCode). I know I could create multiple parameters (e.g. ...WHERE CustomerCode in (@CustCode1, @CustCode2,...etc), but do not want to limit the number of customers.

    If I set CustCode to be KA1001, everything works fine. If I set CustCode to be KA1001, KA1002 it does not return any records.

    I think the problem is in the way SQL Server concatenates the stored procedure before execution. Is what I am attempting to do possible? Is there any particular format I need to set the string parameter to? I've tried:

    KA1001', 'KA1002 (in the hope SQL Server just puts single quotes either side of the string)

    and

    'KA1001', 'KA1002'

    Both fail

    Any ideas?

    Regards

    Xo

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you need to parse your list into a table variable and then join to that table variable.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2007
    Posts
    62
    How about

    WHERE
    ','+@CustCode+','
    LIKE
    '%,'+CustomerCode+',%'

    perhaps?

    It's crap (won't use an index) but does work and is easy (might need some trims in there).
    These requirements are really dodgy but sadly very common. You shoudl really try using an array of some kind. I would stay away from dynamic SQL which is the other way.

  4. #4
    Join Date
    Dec 2003
    Posts
    46
    You can also do this by passing the list of parameters as nText and using XML to add as many options you want. In the sproc you will need to use sp_xml_PrepareDocument to force that into a local temp table.

    It's all kinda tricky, but crafty at the same time.

  5. #5
    Join Date
    Jul 2007
    Posts
    7
    LoztInSpace: Don't quite understand the syntax! Can you post an example! Thanks

    alex8675: Thanks but no thanks!!!

    Thrasymachus: Thanks!! Have used your solution for now

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.

  7. #7
    Join Date
    Feb 2007
    Posts
    62
    Quote Originally Posted by xoqon
    LoztInSpace: Don't quite understand the syntax! Can you post an example! Thanks
    What's to not understand? It's just a where statement. Did you try it?

  8. #8
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Hi Xogon

    A bit of a bumpy ride for your second post methinks - don't take it personal, sometimes people forget what it's like to not fully understand the basics.

    One easy Option for you - but not the most efficient is to build you SQL Statement in a VarChar and then EXECUTE it.

    Heres an example

    Code:
    Declare @SQL VarChar(1000)
     
    SELECT @SQL = ' SELECT [something]'
    SELECT @SQL = @SQL + ' FROM [Table]'
    SELECT @SQL = @SQL + ' WHERE CustomerCode IN ('  + @CustCode + ')'
     
    EXECUTE (@SQL)
    And watch out for the Single Quotes in your @CustCode these will have to be doubled up

    GW
    Last edited by GWilliy; 08-01-07 at 08:10.
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ummmmmmmmmmm...did you read the link I posted?
    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.

  10. #10
    Join Date
    Jul 2007
    Posts
    7
    thanks GWilliy - worked a treat!!

  11. #11
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    UUummmmmmmmmmmmmmmmmm - LOL

    I Obviously did Brett & found the link in the Thread to another thread which had this Code.
    Code:
    CREATE Procedure sp_dynamic_test
    @TableName varChar(100),
    @ID1 varchar(100),
    @ID2 varchar(100),
    @ID3 varchar(100),
    @DX varchar(100),
    @family varchar(100)
    AS
     
    Declare @SQL VarChar(1000)
     
    SELECT @SQL = ' SELECT FIRSTNAME, LASTNAME, @ID1, @ID2, @ID3, @DX '
    SELECT @SQL = @SQL + ' FROM '
    SELECT @SQL = @SQL + @TableName
    SELECT @SQL = @SQL + ' WHERE Family = COALESCE((NULLIF(@Family,0)),Family) ' 
     
    Exec ( @SQL)
    Probably Naughty of me to do the Leg work for xogon, Plagarize & Dumb the code down.

    I don't know what Skill level xogon is @ (2nd Post) but I thought a Concise & Clear example is often a good place to start. Complexities & doing his own Googles will organically follow.

    DBForums is a Fantastic Site but I think sometimes posting a link to a related thread can frustrate New members, Although I will accept a point about too much hand holding.

    hopefuly I've not upset anyone

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  12. #12
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Still, the use of the table variable is MUCH preferred over building dynamic SQL like you are suggesting and as I fear xogon has implemented.

    We actually have built a function that takes as input the CSV string of (in this case, customer numbers), parses it, and returns the required table. That way I offer some reasonable methodology to the unwashed masses of developers here that need to do what you are doing in some form or fashion.

    I didn't check Brett's link, but I think this function was derived from an earlier query I made on this very subject eons ago.
    Code:
    CREATE FUNCTION [dbo].[fn_CSVList_FilteredPortfolioTable] (@CsvList varchar(4000))
    RETURNS table 
    AS
    
    RETURN (  SELECT TOP 100 PERCENT *
    	      FROM dbo.Portfolio (nolock)
    	      WHERE ((CHARINDEX(',' + CAST(PortfolioID AS VARCHAR) + ',', ',' + @CsvList + ',') > 0)
    			AND (isInactive <> 1))
    	      ORDER BY PortfolioID)
    this code is probably even more complex than yours would need to be (you really would only need the CHARINDEX line), as it "verifies" the entries in the input CSV string against a table of valid codes.

    Still, the use of a function lets you do the conversion on the fly in selects by join, such as:
    Code:
    			SELECT DISTINCT CL.PortfolioID, CL.StockOSID, @CreateDate AS CreateDate, BuyDate, SellDate, CL.StockSymbol, Weight  
    			FROM dbo.CurrentList CL (nolock) 
    			INNER JOIN dbo.fn_CSVList_FilteredPortfolioTable(@PortfoliosToProcess) FP ON
    				CL.PortfolioID = FP.PortfolioID
    			WHERE ((@CreateDate > CL.Buydate) AND
    				  (@CreateDate <= CL.SellDate)) AND 
    				(CL.Active = 1)
    errrr...and forgive the use of the fn_ prefix on the function name....it was created before I knew better, and at the suggestion of the senior SQL Server developer here at the time. *blush*
    Last edited by TallCowboy0614; 08-01-07 at 15:55.
    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
  •