locked
Passing a set of values using the SQL 'IN' operator RRS feed

  • Question

  • User-1173210020 posted

     Hi,

    I was wondering if it was possible to pass a dynamic parameter to the the SQL 'IN' operator in a query that is created in the .xsd (data) layer.

     The hardcoded version works like it should: 

    SELECT * FROM t_items WHERE id_item IN (1,2,4)

     The problem is that I need this to be dynamic, thus the 1,2,4 part could be different each time, so 1,3,5,6,7 is possible too, as is an empty parameter.

     I realised I'd need to be able to create extra parameters, or find another way to have the data returned as one block, if at all that is possible.

    I have the ID's in a Set class (created by Seth Peck), to filter out duplicates, can I use this data to create parameters on-the-fly?

     

    Thanks,

     

    Dr_Gonzo

    Friday, September 5, 2008 9:26 AM

Answers

  • User1716267170 posted

    Hi Dr_Gonzo,

    I have the ID's in a Set class (created by Seth Peck), to filter out duplicates, can I use this data to create parameters on-the-fly?

    As far as I know, it can't be achieved. The text will not be parsed.

    The solution to achieve that is to create a stored procedure which can parse the text. For your question, you can pass the collection as a string value into stored procedure. Please take a try.

    Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 8, 2008 5:29 AM

All replies

  • User1716267170 posted

    Hi Dr_Gonzo,

    I have the ID's in a Set class (created by Seth Peck), to filter out duplicates, can I use this data to create parameters on-the-fly?

    As far as I know, it can't be achieved. The text will not be parsed.

    The solution to achieve that is to create a stored procedure which can parse the text. For your question, you can pass the collection as a string value into stored procedure. Please take a try.

    Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 8, 2008 5:29 AM
  • User1537327443 posted

    I know this is a very late reply but I hope this will work if you want to parametarize it. you can use  'COALESCE' as the following:

    declare  @IN_IDs nvarchar(max)

    SELECT @IN_IDs = COALESCE(@IN_IDs+ ',','') + '''' +

    CAST(ID AS varchar(30)) + '''' FROM your_ID_table

    SELECT  * from t_items WHERE ( @IN_IDs) like '%' + '''' + CAST(id_item AS nvarchar(30)) + '''' + '%'

     

     

     

    Sunday, December 30, 2012 1:16 AM