1

I want to pass a csv of int

1,2,3,4,5,6,7

to a stored procedure so that I can use the passed-in values in an IN() so that 

IN(1,2,3,4,5,6,7)

Is that even possible?

Auguste
  • 2,007
  • 2
  • 17
  • 25
wingyip
  • 3,465
  • 2
  • 34
  • 52
  • So the CSV is always going to have 1 row in it of values? – SQLChao Jun 06 '16 at 16:23
  • 1
    you can use dynamic sql, or a split function as pointed out in the flagged answer. When splitting, you can either join onto the table that your split values are stored, or `in (select value from splitValues)` – Kritner Jun 06 '16 at 16:25
  • You might also take a look here. The flagged answer is using a recursive cte which works well enough but it pretty inefficient. Here are some better options. http://sqlperformance.com/2012/07/t-sql-queries/split-strings – Sean Lange Jun 06 '16 at 16:44
  • 1
    What you really want to do is to pass in a Table-Valued Parameter. – RBarryYoung Jun 06 '16 at 17:01

2 Answers2

2

Just in case you simply mean to use a dynamic parameter for your SQL:

CREATE PROCEDURE [dbo].[takeMeAndMySeeEzzBee]
    @csv VARCHAR(30)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(255)
    SET @sql = 'SELECT THIS_AND_THAT FROM HERE WHERE YOU_ARE IN(' + @csv + ')'

    EXEC sp_executesql @sql
END;
GO
-- I will runawayyyy
EXEC [dbo].[takeMeAndMySeeEzzBee] @csv='1,2,3,4'

^ is an example of how dynamic SQL can work.

Now if you mean to pull data from a .csv file, let us know; my answer will change.

RDJ
  • 191
  • 1
  • 7
  • Nice! Only one issue may occur - it needs special rights to execute 'sp_executesql', but maybe this is not an issue to an OP :) here is my up – gofr1 Jun 07 '16 at 17:58
  • @RDJ I want to pass .csv to SP and read data, so can you please give solution for that also – S. Deshmukh Aug 01 '23 at 10:49
0

You can use XML:

CREATE PROCEDURE dbo.MyProcName
    @csv nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @xml xml

    SELECT @xml = CAST('<a>' + REPLACE(@csv,',','</a><a>') +'</a>' as xml)

    SELECT what_you_need
    FROM some_table s
    INNER JOIN (
        SELECT t.v.value('.','int') as something
        FROM @xml.nodes('/a') as t(v)
        ) as p
        ON p.something = s.something
END

Or:

SELECT what_you_need
FROM some_table s
WHERE s.something IN (
    SELECT t.v.value('.','int') as something
    FROM @xml.nodes('/a') as t(v)
    )
gofr1
  • 15,741
  • 11
  • 42
  • 52