SP - CSV String as input parameter

• Bill Maners

  Ten Centuries

  Points: 1340

  More actions

  January 8, 2006 at 6:43 pm

  #166677

  Hi, All,

  I have a stored Procedure being called from a Crystal Report that receives several parameters.  On of which is a string containing a list of values.  e.g. 'v1, v2, v3'

  The stored procedure need to perform a select at the end which would look like:

  Select * from table where fld in @sList

  Where @sList is 'v1, v2, v3'

  Thanks in advance for the help,

  Bill

• [KH]

  SSCommitted

  Points: 1908

  More actions

  January 8, 2006 at 7:41 pm

  #613449

  declare @sListvarchar(100), @sqlcmd nvarchar(1000)

  select @sList = 'v1,v2,v3'

  select @sqlcmd = 'select * from table where fld in ' + replace('(''' + @sList + ''')', ',', ''',''')

  exec (@sqlcmd)

• Chris Hedgate

  One Orange Chip

  Points: 25041

  More actions

  January 9, 2006 at 2:01 am

  #613471

  Split the values using a function such as the following:

  CREATE FUNCTION dbo.fnListToSet (@list VARCHAR(8000), @delimiter VARCHAR(3))

  RETURNS TABLE

  AS

  RETURN

  SELECT s.tuple

  FROM (

  SELECT SUBSTRING(

  @delimiter + @list + @delimiter

  , numbers.n + LEN(@delimiter)

  , CHARINDEX(@delimiter, @delimiter + @list + @delimiter, numbers.n + LEN(@delimiter)) - numbers.n - LEN(@delimiter)

  ) AS tuple

  FROM (

  SELECT D0.d*1 + D1.d*10 + D2.d*100 + D3.d*1000

  FROM (SELECT 0

  UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6

  UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D0 (d)

  , (SELECT 0

  UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6

  UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D1 (d)

  , (SELECT 0

  UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6

  UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D2 (d)

  , (SELECT 0

  UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6

  UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D3 (d)

  ) AS numbers (n)

  WHERE SUBSTRING(

  @delimiter + @list + @delimiter

  , numbers.n

  , LEN(@delimiter)

  ) = @delimiter

  AND n 0

  GO

  Now you can do:

  SELECT foo.* FROM foo

  INNER JOIN (SELECT * FROM dbo.fnListToSet(@sList, ',')) bar

  ON foo.fld = bar.tuple

  --
  Chris Hedgate http://www.hedgate.net/
  Contributor to the Best of SQL Server Central volumes
  Articles: http://www.sqlservercentral.com/columnists/chedgate/

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  January 9, 2006 at 7:22 am

  #613528

  Nicely done...

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Bill Maners

  Ten Centuries

  Points: 1340

  More actions

  January 9, 2006 at 7:56 am

  #613539

  Thank you all for you replies,

  Bill