Insert new row into table for each value in the comma separated string

Question

Insert new row into a table for each value in the comma separated string values passed to a SP

Vishal Yelve · Answer

Hi Deepak, DECLARE @DepartmentId INT = 123; DECLARE @str NVARCHAR(100) = 'Student1, Student2, Student3'; DECLARE @t TABLE(xstr XML); INSERT INTO @t VALUES (CAST(' ' + REPLACE(@str, ', ', ' ') + ' ' AS XML)) SELECT T.r.value('.','varchar(100)') as student, @DepartmentId AS DepartmentId, 'Attended' AS Comments, GETDATE() AS [Date] FROM @t OUTER APPLY xstr.nodes('/students/student') t(r) ---------------- Create Function in DB. CREATE FUNCTION SplitString ( @Input NVARCHAR(MAX), @Character CHAR(1) ) RETURNS @Output TABLE ( Item NVARCHAR(1000) ) AS BEGIN DECLARE @StartIndex INT, @EndIndex INT SET @StartIndex = 1 IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) @Character BEGIN SET @Input = @Input + @Character END WHILE CHARINDEX(@Character, @Input) > 0 BEGIN SET @EndIndex = CHARINDEX(@Character, @Input) INSERT INTO @Output(Item) SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1) SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input)) END RETURN END GO SP CREATE PROCEDURE GetEmployees @EmployeeIds VARCHAR(100) AS BEGIN SELECT FirstName, LastName FROM Employees WHERE EmployeeId IN( SELECT CAST(Item AS INTEGER) FROM dbo.SplitString(@EmployeeIds, ',') ) END EXEC GetEmployees '1,3,5,9'