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

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'