Sql Query RSS

• • Reply
  

  sailakshmi.e

  Member

  5 Points

  33 Posts

  Sql Query

  Jan 25, 2013 06:47 AM|LINK

  Hi,

  From the application i am getting multiple ids as string. I need to pass the same to my sql query..

  Ex: declare @EmpId = '1,2,3,4'

  Select * from Employee Where EmpId IN (@EmpId)

  It is throwing an error.. In my Employee table EmpId is an integer but i am getting the parameters as string how i can do this..

  Please help me..

• • Reply
  

  me_ritz

  Star

  9337 Points

  1447 Posts

  Re: Sql Query

  Jan 25, 2013 07:29 AM|LINK

  declare @EmpId varchar(20)
  set @EmpId = '1,2,3,4'
  
  declare @qry varchar(1000)
  
  set @qry = 'Select * from Employee Where EmpId IN (' + @EmpId + ')'
  
  exec(@qry)

  Please mark the post as "Answer" that helps you.
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: Sql Query

  Jan 25, 2013 07:32 AM|LINK

  sailakshmi.e

  Ex: declare @EmpId = '1,2,3,4'

  Select * from Employee Where EmpId IN (@EmpId)

  Lots of thread available for this,

  you can look up solved thread.

  http://forums.asp.net/t/1549499.aspx

  http://forums.asp.net/t/1519314.aspx

  http://forums.asp.net/t/1770975.aspx/1

  http://forums.asp.net/t/1681599.aspx/1

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

  sandeepmitta...

  Contributor

  6801 Points

  1059 Posts

  Re: Sql Query

  Jan 26, 2013 02:55 AM|LINK

  There are multiple options

  1st as suggested by Ritesh using dynamic query

  2nd using split function and joins

  First Refer this link and create split function

  http://itdeveloperzone.blogspot.in/2012/07/split-function-in-sql.html

  Then, write query as

  declare @EmpId = '1,2,3,4'
  Select *
  from Employee
  Where EmpId IN (select val from dbo.split(@EmpId,','))
  --or
  Select *
  from Employee e
  inner join dbo.split(@EmpId,',') s on e.EmpId = s.val

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on Jan 26, 2013 02:56 AM
  • Edited by sandeepmittal11 on Jan 26, 2013 02:56 AM
• • Reply
  

  sailakshmi.e

  Member

  5 Points

  33 Posts

  Re: Sql Query

  Jan 28, 2013 07:56 AM|LINK

  Thanks its working fine.

  declare @EmpId = '1,2,3,4' 
  Select * 
  from Employee 
  Where EmpId IN (ISNULL((select val from dbo.split(@EmpId,',')),EmpId)

  When i am running the above query i am getting the following error

  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

  Please help me..

• • Reply
  

  me_ritz

  Star

  9337 Points

  1447 Posts

  Re: Sql Query

  Jan 28, 2013 08:00 AM|LINK

  sailakshmi.e

  declare @EmpId
  = '1,2,3,4'
  
  Select *
  
  from Employee
  
  Where EmpId IN (ISNULL((select val from dbo.split(@EmpId,',')),EmpId)

  declare @EmpId = '1,2,3,4'
  Select *
  from Employee
  Where EmpId IN (select val from dbo.split(@EmpId,','))
  

  
  Refer to sandeepmittal11 post carefully.

  Please mark the post as "Answer" that helps you.
• • Reply
  

  maheshvishnu

  Member

  164 Points

  65 Posts

  Re: Sql Query

  Jan 29, 2013 04:03 AM|LINK

  declare @EmpId = '1,2,3,4'

  Select * from Employee Where EmpId IN (@EmpId)

  • Marked as answer by sailakshmi.e on Jan 29, 2013 08:26 AM