pass list of values as parameter to stored proc

• naveenka

  SSC Enthusiast

  Points: 174

  More actions

  August 15, 2003 at 2:21 pm

  #64809

  Hello,

  I am trying to pass a list (list of values) as a parameter to Stored Procedure and so far no luck getting it to work.

  here is an example of what I am trying to do.

  CREATE PROCEDURE sp_Proc1

  @listofValues As varchar(50)

  select * from tbl1

  where col1 in (@listofvalues)

  Is this possible? if so how?

  Thanks,

  Naveen/

  declare @valList varchar(30)

  Set @valList = '101' + ',' + '102' + ',' + '103'

  exec sp_Proc1

  Naveen Abraham

  
  Naveen Abraham

• NPeeters

  SSChampion

  Points: 12610

  More actions

  August 15, 2003 at 2:27 pm

  #470153

  This is only possible using a dynamic query.

  Try this:

  
  
  CREATE PROCEDURE sp_Proc1
  
  @listofValues As varchar(50)
  
  AS
  
  DECLARE @stmt varchar(4000)
  
  SET @stmt = 'select * from tbl1 where col1 in (' + @listofvalues + ')'
  
  EXEC @stmt
  
  

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  August 15, 2003 at 2:28 pm

  #470154

  Hi Naveen,

  unfortunately it is not possible to pass something like ParamArray in VB to a sproc.

  However, you have two option available. Split string before passing. I think you can go up to some 2000 separate parameters, or split the string in your proc.

  Maybe you also have to use the dynamic approach

  Something like

  EXEC sp_executeSQL @yourstring

  HTH

  Cheers,

  Frank

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• NPeeters

  SSChampion

  Points: 12610

  More actions

  August 15, 2003 at 2:29 pm

  #470155

  There should be some stuff in the Scripts section about this.

  An alternative is parsing the list of values in a temporary table (or table variable) and joining the source table against it.