HOW TO SPLITE COMMA SEPERATED STRING AND INSERT INTO TEMP TABLE USING PROCEDURE

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[splite_insert_into_table]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].splite_insert_into_table
GO
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].splite_insert_into_table (@autostates_in_mlp NVARCHAR(4000))
AS
BEGIN

       DECLARE @OrderID varchar(10), @Pos int

    SET @autostates_in_mlp = LTRIM(RTRIM(@autostates_in_mlp))+ ','
    SET @Pos = CHARINDEX(',', @autostates_in_mlp, 1)
    --PRINT @autostates_in_mlp
    --PRINT @Pos


     IF OBJECT_ID('tempdb..#temp_auto_states_in_ML') IS NOT NULL
    DROP TABLE #temp_auto_states_in_ML

    CREATE TABLE #temp_auto_states_in_ML(state_cd CHAR(2))
    IF REPLACE(@autostates_in_mlp, ',', '') <> ''
    BEGIN
        WHILE( @Pos > 0)
        BEGIN
            SET @OrderID = LTRIM(RTRIM(LEFT(@autostates_in_mlp, @Pos - 1)))--WY
            IF @OrderID <> ''
            BEGIN
                INSERT INTO #temp_auto_states_in_ML (state_cd)
                VALUES (CAST( @OrderID AS VARCHAR(10))) --Use Appropriate conversion
            END
            SET @autostates_in_mlp = RIGHT(@autostates_in_mlp, LEN(@autostates_in_mlp) - @Pos)
            SET @Pos = CHARINDEX(',', @autostates_in_mlp, 1)

END
    SELECT * FROM  #temp_auto_states_in_ML
END

END

TESTING :
EXEC splite_insert_into_table @autostates_in_mlp='WY'
EXEC  splite_insert_into_table  @autostates_in_mlp='WY,NA,BA,NA'

--------------------another simple  way

DECLARE @autostates_in_mlp VARCHAR(256)
SET @autostates_in_mlp='WY,NA'
DECLARE @starting_position INT
   
     DECLARE @Previous_position INT

     SET @starting_position=1
     SET @Previous_position=1
      SET @autostates_in_mlp = LTRIM(RTRIM(@autostates_in_mlp))+ ','

      SELECT @starting_position= CHARINDEX(',',@autostates_in_mlp,@Previous_position)

    IF OBJECT_ID('tempdb..#temp_auto_states_in_ML') IS NOT NULL
    DROP TABLE #temp_auto_states_in_ML

    CREATE TABLE #temp_auto_states_in_ML(state_cd CHAR(2))


WHILE (@starting_position>0)
BEGIN
     INSERT INTO #temp_auto_states_in_ML(state_cd)
     SELECT SUBSTRING (@autostates_in_mlp,@Previous_position,@starting_position-@Previous_position)

     SET @Previous_position=@starting_position+1
     SELECT @starting_position = CHARINDEX (',',@autostates_in_mlp,@Previous_position)
END