In Operator with Variable

• Galahad Phillips

  Mr or Mrs. 500

  Points: 551

  More actions

  August 21, 2006 at 11:29 am

  #116066

  Hello,

  I have a little issue that I'm trying to resolve that may be simpler than I'm making it. So here goes...

  I have a sql statement listed below:

  select * from tablename

  where ID in (2,3)

  I want to make this statement more flexible so I can use a variable instead of the (2,3):

  declare @var varchar(10)

  set @var = ‘2,3’ 

  select * from tablename

  where ID in (@Var)

  This statement returns no records. When the variable is set to a single value such as 2 or 3, the statement

  returns data for those records matching a value of 2 or 3.

  What I am doing wrong here? Should I get rid of the IN operator all together?

  Some notes on the table:

  ID is datatype int and contains values of 0-3. Values of 0 and 1 are invalid for this statement.

  Thanks in advance,

  GP

   

   

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  August 21, 2006 at 11:52 am

  #655953

  http://www.sommarskog.se/dynamic_sql.html

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• Galahad Phillips

  Mr or Mrs. 500

  Points: 551

  More actions

  August 21, 2006 at 11:56 am

  #655955

  I wish I could wish dynamic sql...but it is frowned on at my work. Any other ideas?

• Gopi Muluka

  SSCarpal Tunnel

  Points: 4594

  More actions

  August 21, 2006 at 12:03 pm

  #655956

  try this

   

  declare

  @var varchar(10), @sql nvarchar(500)

  set

  @var = ‘2,3’

  set

  @sql='select * from tablename where ID in (' + @Var + ')'

  exec

  sp_executesql @sql

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  August 21, 2006 at 12:41 pm

  #655962

  Use fn_split()

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=302389#bm302391

   

• Galahad Phillips

  Mr or Mrs. 500

  Points: 551

  More actions

  August 21, 2006 at 2:05 pm

  #655978

  the split function worked well...thanks alot