Passing A List Of Values As Parameter To Sql Server Procedure

Here is an example of how to pass array of values into SQL Server stored procedure using XML.

--================================================
--==  DATABASE LIST AS A SINGLE PARAMETER
--================================================
ALTER PROCEDURE usp_db_refresh_test_1 
      (@dblist varchar(1000))
AS
 
--optional
--SELECT @dblist = REPLACE(@dblist,' ','')
 
DECLARE @xml xml
SELECT @xml = CONVERT(xml,'<root><s>' + REPLACE(@dblist,',','</s><s>') + '</s></root>')
SELECT [Value] = T.c.value('.','varchar(200)')
FROM @xml.nodes('/root/s') T(c)
--here you can map 
 
EXEC usp_db_refresh_test_1 'database1, data-base2, database3, database4, database5'
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License