How to insert comma separated value in a table using Stored procedure

Are you looking for a way to insert comma separated value in a table using SQL Stored Procedure? This article will help you to insert comma separated value in a different row. I thought I will write an article with full details because I used to see this question in Microsoft forums. 

Let’s start by creating a sample table first.

 

--Create table: 

CREATE TABLE tblNoOfAttempt(UserID INT IDENTITY,NoOfAttemp VARCHAR(50)) 

Since we have the table ready we will create the stored procedure to insert the comma separated value in our table.

 

-- Create Stored Procedure: 

Create PROCEDURE sp_InsertNoOfAttempt 

-- Add the parameters for the stored procedure here 

      @NoOfAttempt VARCHAR(100) 

AS 

BEGIN 

SET NOCOUNT ON; 

declare @XML xml 

SELECT @XML=CONVERT(xml,'<root><s>' + REPLACE(@NoOfAttempt,',','</s><s>') + '</s></root>') 

INSERT INTO tblNoOfAttempt 

SELECT [Value] = XM.SP.value('.','varchar(50)') 

FROM @XML.nodes('/root/s')XM(SP) 

END 

GO 

Now it’s the time to execute the stored procedure by passing some comma separated value.

-- Execute stored procedure by passing the comma separated value 
sp_InsertNoOfAttempt '8,7,10,9,5' 

Since the first field is an identity column we don’t need to pass the parameter for that. Value will be inserted automatically.

At last let’s see how the value is inserted in to he table by running select query.

-- Select the table values to see the out put 

select * from tblNoOfAttempt

Out put of the above query will be,