Example:
I have created one procedure which has one parameter VARCHAR(100). This procedure contains the parameter having value like '1,5,6,20'. The requirement is: Update the records having ID = 1,5,6,20 (Any of them). This is the one way that we can prepare the SQL query.
Proposed Solution:
Convert VARCHAR variable to XML as follows:
SET @xmlIDs = ' ' + REPLACE(@str, ',', '') + '' + ''
So this statement will generate XML from VARCHAR value as follows:
1 6 7 8 20
So, Now this is the XML, which can be easily read in SQL SERVER (2005 and above) as:
SELECT x.v.value('.','INT') FROM @xmlIDs.nodes('/IDs/ID') x(v)
This will give me result set as ( as a separate table)
So that’s it, Now I can easily use this result set in my query, to update the rows accordingly.
So my Procedure looks like:
CREATE PROC Test_ReadValuesFromCommaSeparatedVariable @str VARCHAR(100) AS DECLARE @XmlIDs XML SET @xmlIDs = ' ' + REPLACE(@str, ',', '') + '' + '' UPDATE TableName SET Flag = 1 WHERE ID IN( SELECT x.v.value('.','INT') FROM @xmlIDs.nodes('/IDs/ID') x(v) )
So, it is very easy to read values from Comma separated value.
Let me know if it helps you.
Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.
Posted by Tejas Shah on November 22, 2017
Posted by Tejas Shah on November 11, 2017
Posted by Tejas Shah on August 12, 2015
Posted by Tejas Shah on June 26, 2015
Posted by Tejas Shah on April 20, 2015