Pass CSV string as parameter to your SQL Server stored procedure

Below is an example procedure showing a way to pass a csv string to a stored procedure in SQL Server. In this case, I needed to use the CSV string as items in an 'IN' clause. To do so, I used SQL Server's XML capability. To call the procedure: USE your_db GO EXEC pass_csv_as_param @table = 'your_table' ,@idx_csv_str = 'idx1_name,idx2_name'

ALTER PROCEDURE pass_csv_as_param 
@table varchar(MAX),
@idx_csv_str varchar(max) = NULL
AS

--XML variable
DECLARE @XML xml;

-- Here is where we actually transform the csv into XML. Later (SQL below) we query from the xml variable.
SET @XML = CAST('' + REPLACE(@idx_csv_str, ',', '') + '' AS XML)

SELECT
 a.name AS indexName,
 c.name AS schemaName,
 b.name AS tableName
FROM sys.indexes a
JOIN sys.objects b
 ON a.object_id = b.object_id
JOIN sys.schemas c
 ON (c.schema_id = b.schema_id)
WHERE a.type_desc = 'NONCLUSTERED'
AND UPPER(b.name) = UPPER(@table)

-- here is where we query from the xml values to use in the in statement. As a bonus,
-- we also accomodate cases where the @idx_csv_str is null or not passed. When that occurs
-- we return all indexes from the table.

AND (a.name IN (SELECT x.i.value('.', 'VARCHAR(MAX)') FROM @XML.nodes('i') x(i))
OR a.name = CASE WHEN @idx_csv_str IS NULL THEN a.name ELSE '' END)