Split Function in Sql Server

We can write function in Sql Server,

CREATE FUNCTION Split

(

    @String nvarchar(4000),

    @Delimiter char(1)

)   

RETURNS

    @Results TABLE (Items nvarchar(4000))   

AS

    BEGIN   

    DECLARE @INDEX INT   

    DECLARE @SLICE nvarchar(600)   

    -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z   

    --     ERO FIRST TIME IN LOOP   

    SELECT @INDEX = 1   

    -- following line added 10/06/04 as null   

    --      values cause issues   

    IF @String IS NULL RETURN   

    WHILE @INDEX !=0    

        BEGIN    

         -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER   

         SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)   

         -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE   

         IF @INDEX !=0   

          SELECT @SLICE = LEFT(@STRING,@INDEX - 1)   

         ELSE   

          SELECT @SLICE = @STRING   

         -- PUT THE ITEM INTO THE RESULTS SET   

         INSERT INTO @Results(Items) VALUES(@SLICE)   

         -- CHOP THE ITEM REMOVED OFF THE MAIN STRING   

         SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)   

         -- BREAK OUT IF WE ARE DONE   

         IF LEN(@STRING) = 0 BREAK   

    END   

    RETURN   

END

Then We can call that function from our stored procedure like,

Declare @input varchar(255)

Set @input = 'EMP101,EMP155,EMP199'

 --create table #Temp1 (sno int identity(1,1), empno varchar(100)) 

select ID= IDENTITY(int, 1,1),* into #Temp from dbo.Split(@input,',')

select * from #Temp

drop table #Temp