Passing a set of values using the SQL 'IN' operator RSS

• • Reply
  

  Dr_Gonzo

  Member

  2 Points

  8 Posts

  Passing a set of values using the SQL 'IN' operator

  Sep 05, 2008 01:26 PM|LINK

   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
  

  sql Parameter Passing parameters C# in

  • Edited by Dr_Gonzo on Dec 30, 2012 05:17 AM
  • Edited by Dr_Gonzo on Dec 30, 2012 05:17 AM
  • Edited by Dr_Gonzo on Dec 30, 2012 05:21 AM
  • Edited by Dr_Gonzo on Dec 30, 2012 05:21 AM
• • Reply
  

  Wencui Qian ...

  All-Star

  56784 Points

  5796 Posts

  Microsoft

  Re: Passing a set of values using the SQL 'IN' operator

  Sep 08, 2008 09:29 AM|LINK

  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.

  Please mark the replies as answers if they help or unmark if not.
  
  If you have any feedback about my replies, please contact msdnmg@microsoft.com
  
  Microsoft One Code Framework
  
  • Edited by Wencui Qian - MSFT on Sep 11, 2008 12:06 PM
  • Edited by Wencui Qian - MSFT on Dec 30, 2012 05:17 AM
  • Marked as answer by Wencui Qian - MSFT on Dec 30, 2012 05:21 AM
• • Reply
  

  MohanadHadda...

  Member

  2 Points

  1 Post

  Re: Passing a set of values using the SQL 'IN' operator

  Dec 30, 2012 05:16 AM|LINK

  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)) + '''' + '%'

   

   

   

  • Edited by MohanadHaddadin on Dec 30, 2012 05:17 AM
  • Edited by MohanadHaddadin on Dec 30, 2012 05:17 AM
  • Edited by MohanadHaddadin on Dec 30, 2012 05:21 AM
  • Edited by MohanadHaddadin on Dec 30, 2012 05:21 AM