Comma seperated values to Stored Procedure

 Comma seperated values to Stored Procedure



In this stored procedure all the comma seperated values are inserted into one of the temporary table and then these values are used for further query




CREATE PROC dbo.GetEmployee
(
    @EmployeesList varchar(500)
)
AS
BEGIN
    SET NOCOUNT ON

    CREATE TABLE #TempEmpList
    (
        EmployeeID int
    )

    DECLARE @EmployeeID varchar(10), @iPos int

    SET @EmployeesList = LTRIM(RTRIM(@EmployeesList))+ ','
    SET @iPos = CHARINDEX(',', @EmployeesList, 1)

    IF REPLACE(@EmployeesList, ',', '') <> ''
    BEGIN
        WHILE @iPos > 0
        BEGIN
            SET @EmployeeID = LTRIM(RTRIM(LEFT(@EmployeesList, @iPos - 1)))
            IF @EmployeeID <> ''
            BEGIN
                INSERT INTO #TempEmpList  (EmployeeID) VALUES (CAST(@EmployeeID AS int)) 
            END
            SET @EmployeesList = RIGHT(@EmployeesList, LEN(@EmployeesList) - @iPos)
            SET @iPos = CHARINDEX(',', @EmployeesList, 1)

        END
    END   

    SELECT E.EmployeeID, ReportingID,JoiningDate
    FROM     dbo.Employees AS E
        JOIN
        #TempEmpList t
        ON E.EmployeeID = t.EmployeeID
       
END
GO