Dynamic comma-separated parameters in SQL Server

Question

I'm using SQL Server 2008 and I'm passing a parameter @list which contains '1,2,3,4' as values to a stored procedure. The @list should be converted to '1','2','3','4'. Which query do I have to use for that?

Answer 1

I am using a solution like this :

DECLARE @list VARCHAR(100) = '1,2,3,4'

SET @list = '<N>' + Replace(@list, ',', '</N><N>') + '</N>'
;WITH cte
     AS (SELECT c1.value('.', 'varchar(30)') AS List
         FROM   (SELECT Cast(@list AS XML)) t(c)
                CROSS apply c.nodes('/N') AS t1(c1))
SELECT *
FROM   cte
--join abc on cte.List = abc.Column

note : if you simply want to put the numbers between single quotes then :

DECLARE @list VARCHAR(100) = '1,2,3,4'
SET @list = '''' + REPLACE(@list, ',', ''',''') + ''''
SELECT @list