Post reply

Unlimited user entered values in spr where clause

  • October 7, 2002 at 7:52 am

    #78888

    I am trying to create a sproc where the user chooses any number of product codes from the interface and all of these product codes are passed to the sproc and used as filter criteria. Here is what I have tried - creating a string on through the interface that gets passed to a varchar variable in the sproc, then using the IN operator to search that string and make sure the product is in there before selecting that record. How else is there to do it and what am I doing wrong - here is my "testing" code . . .

    DECLARE @prodlist VARCHAR(8000)

    SET @prodlist = 'EP01,EP02,EP03'

    SELECT * FROM vCustProds WHERE chProdCode IN (@prodlist)

    I am very new to t-sql, so I need all the help I can get - THANKS so much!

    Denise

    Denise ;o)


    Denise ;o)

  • October 7, 2002 at 9:20 am

    #437949

    Your product list is a char field. So each value will need quotes around it, so I think I might consider trying this:

    DECLARE @prodlist VARCHAR(8000)

    SET @prodlist = '''EP01'',''EP02'',''EP03'''

    SELECT * FROM vCustProds WHERE chProdCode IN (@prodlist)

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • October 7, 2002 at 11:03 am

    #437970

    The problem is that you have one variable for the values which when submit will interpret as

    IN ('ALLTHEVALUESASONESTRNG')

    To get around this you have at least two options.

    1) Build the query in a dynamic fasion then use EXEC(dynamicquery) to perform query as intended.

    2) Generate a temp table wit a column for the vlaues, the WHILE loop thru the incoming data to parse into seperate rows in them temp table. Finally perform your query

    SELECT * FROM XXX WHERE VALS IN (SELECT VALCOL FROM tmpTbl)

    There are several threads on this site about both but if you need more detail just let me know as I don't have in front of me here but can lookup the threads I recall.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Viewing 3 posts - 1 through 3 (of 3 total)

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