How to pass multiple values through one parameter in a stored procedure

• Samkris

  SSC Enthusiast

  Points: 165

  More actions

  May 22, 2014 at 2:43 am

  #293653

  Below is my Script:

  ALTER PROCEDURE [dbo].[spPOExport]

  @BST_BESTELLUNG INT

  AS

  BEGIN

  SELECT

  BH.BST_LIEFERANT AS SUPPLIER,

  BZ.BDT_REFNUMMER AS ITEM_REF,

  BZ.BDT_ANZ_RESTMENGE AS OUTSTANDING_QTY,

  BZ.BDT_EKP_BESTELLT AS PURCHASE_PRICE,

  BH.BST_WAEHRUNG AS CURRENCY

  FROM BESTHEAD BH

  INNER JOIN BESTZEIL BZ ON BH.BST_ORIGNR=BZ.BDT_ORIGNR AND BH.BST_BESTELLUNG=BZ.BDT_BESTELLUNG

  WHERE

  BST_FILIALE = 150

  AND

  BST_STATUS IN(1,2)

  AND

  BZ.BDT_ANZ_RESTMENGE > 0

  AND

  BH.BST_BESTELLUNG=@BST_BESTELLUNG

  END

  I need to pass multiple values as input parameter

  For eg: exec spPOExport 1,2,3,4.

  Does anybody see anything wrong with what I have here and if so, have a solution?

  Thanks.

• Koen Verbeeck

  SSC Guru

  Points: 259165

  More actions

  May 22, 2014 at 2:52 am

  #1715598

  You are passing a string, not a set of integers.

  Instead, I would try using table valued parameters.

  Passing table valued parameters in SQL Server 2008

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  May 22, 2014 at 4:58 am

  #1715634

  you can try using table valued parameter for this...

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• gbritton1

  SSCertifiable

  Points: 6522

  More actions

  May 22, 2014 at 5:59 am

  #1715658

  You could also pass the parameter as a string "1,2,3,4" and use Jeff Moden's CSV splitter to parse it.

  http://www.sqlservercentral.com/articles/Tally+Table/72993//url%5D