SELECT IN failing

• Andy Knight

  SSC-Addicted

  Points: 433

  More actions

  November 26, 2008 at 9:26 am

  #154952

  Hi,

  can anyone spot whats wrong with this simple SELECT IN

  I have a stored proc which has a parameter call @Business_Line_Ids

  Im calling the stored proc:

  sp_GetBusinessLines @Business_Line_Ids='12, 13, 19, 20'

  And the WHERE clause in the stored proc says:

  WHERE

  tblBusinessLines.Id IN (@Business_Line_Ids)

  but the error I get back is:

  conversion failed when converting the varchar value '12, 13, 19, 20' to data type int.

  So how else can I pass a group of Ids in?

  Any ideas?

  Kind Regards

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  November 26, 2008 at 9:29 am

  #903876

  You can't pass a list like that directly. You can do it with either dynamic SQL, or splitting that comma delimited list. There are advantages and disadvantages to each method.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 26, 2008 at 9:29 am

  #903878

  You can't use a variable in an in clause like that. The in has to either be literals or a subquery.

  Two options:

  1) Dynamic SQL with all of its downsides and risks

  2) A split function that will take that string and turn it into a table that you can put in the IN. There are several in the library here.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass