Post reply

Passing a stored procedure parameter into an IN clause

  • July 30, 2007 at 6:40 am

    #98486

    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

  • July 30, 2007 at 7:48 am

    #723303

    can you post your script? both the sql and the vb (only where you are calling the stored proc)

    you might be able to get round the problem by using dynamic sql,which of coz comes with its own baggage

    Everything you can imagine is real.

  • July 30, 2007 at 8:21 am

    #723318

    Hi bledu

    I've put the values into a temporary table and the sp looks at this! Problem solved!!!

    Thanks anyway

    Xo

  • July 30, 2007 at 8:32 am

    #723319

    how are you using the temp table. is it a multi user app?mmm...

    Everything you can imagine is real.

  • July 30, 2007 at 9:47 am

    #723338

    as well as the code values the temp table has a UserID field!!

  • July 30, 2007 at 10:06 am

    #723347

    you could have used a UDF that returns a table with the values you want. I assume you first insert the values , and the user id then run your stored proc.

    what is the max number of cust ids you can query at any one time

    Everything you can imagine is real.

  • July 31, 2007 at 1:14 am

    #723482

    Easiest might be to load your query into a string variable and exec the variable in your proc.

    Eg.

    declare @SQL as varchar(8000)

    set @SQL = 'Select * from Customers WHERE CustomerCode IN (' + @CustCod + ') '

    Exec( @SQL )

     

  • July 31, 2007 at 7:14 am

    #723554

    David, I believe the easiest way to deal with this situation would be to use the data flow task.  Given this would mean multiple sql statements for each line, but it would accomplish the task.  Assuming you have a table with the id being then value you are pulling in (ie KA...) you would set that table to be the source in a new dataflow task.  You can then add an oledb command and replace the area inside the IN statement with a question mark.  The task will give you the option in the mapping as to what you want that question mark to be and you can simply specify the table id. 

    Hope this helps,

    jim

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply