IN and @VariableName

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  September 11, 2008 at 8:23 am

  #70754

  Hi,

  I got struck up with the below query.

  Create table Emp

  (

  Eno varchar(25),

  EmpCode varchar(1)

  )

  Insert into Emp

  Select '1','C'

  union all

  Select '2','C'

  union all

  Select '3','D'

  union all

  Select '4','D'

  union all

  Select '5','E'

  My query is :

  Create proc P1_Show_EmpDetails

  (

  @EmpCode varchar(10) -- User may give more than one value as COMMA SEPERATED value

  )

  AS

  BEGIN

  Select Eno

  from Emp

  where EmpCode in (@EmpCode)

  End

  The above Proc is not showing any result. Simply it is showing '0 Rows....'.

  Exec P1_Show_EmpDetails 'D,E'

  I think Tally table may be the best way to split the input parameter and checking against Emp table.

  But i may wrong.

  Inputs are welcome !

  karthik

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  September 11, 2008 at 8:37 am

  #870363

  I got the solution.

  Create proc P1_Show_EmpDetails

  (

  @EmpCode varchar(10) -- User may give more than one value as COMMA SEPERATED value

  )

  AS

  BEGIN

  Declare @_EmpCode varchar(10)

  Select @_EmpCode = ','+ @EmpCode +','

  Select Eno

  from Emp

  where patindex('%,' + EmpCode +',%' , @_EmpCode) <> 0

  End

  karthik