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)
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值