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)