DECLARE @Temp TABLE(EmpID INT, MsgID INT)
DECLARE @TempEmpID TABLE(EmpID INT)

INSERT INTO @Temp
SELECT 0, 0 UNION ALL
SELECT 1, 0 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 0 UNION ALL
SELECT 4, 0 UNION ALL
SELECT 5, 0 UNION ALL
SELECT 6, 0 UNION ALL
SELECT 7, 0 UNION ALL
SELECT 8, 0 UNION ALL
SELECT 9, 0 UNION ALL
SELECT 10, 0 UNION ALL
SELECT 11, 0 UNION ALL
SELECT 12, 0 UNION ALL
SELECT 13, 0 UNION ALL
SELECT 14, 0 UNION ALL
SELECT 15, 0 UNION ALL
SELECT 16, 0 UNION ALL
SELECT 17, 0 UNION ALL
SELECT 18, 0 UNION ALL
SELECT 19, 0 UNION ALL
SELECT 20, 0 UNION ALL
SELECT 21, 0 UNION ALL
SELECT 22, 0 UNION ALL
SELECT 23, 0 UNION ALL
SELECT 24, 0 UNION ALL
SELECT 25, 1 UNION ALL
SELECT 26, 2 UNION ALL
SELECT 27, 3


DECLARE @ID VARCHAR(500)
SET @ID = '1,10,40,25,30,50,16'

--Previous Value(MsgID) for certain EmpID in table is 0 and now we are updating with 2 for the EmpID
UPDATE @Temp
SET MsgID = 2
WHERE  ',' + @ID + ',' LIKE '%,' + CAST(EmpID AS VARCHAR) + ',%'
--WHERE ',1,10,40,25,30,50,16,' LIKE '%,EmpID,%'
AND MsgID = 0


SELECT * FROM @Temp

--After updation we have to delete the records whose MsgID = 0 (OldValue)
DELETE FROM
@Temp
WHERE  ',' + @ID + ',' NOT LIKE '%,' + CAST(EmpID AS VARCHAR) + ',%'
AND MsgID = 0

SELECT * FROM @Temp

--Remove Updated EmpID from the variable @ID for Insertion
--Now this variable @ID contains the EmpID 's that we have to insert into the table
SELECT @ID = 
       CASE
			  --'1,2,3' --> '1,3'
              WHEN CharIndex( ',' + CAST(EmpID AS VARCHAR) +',', @ID) <> 0
              THEN REPLACE(@ID, CAST(EmpID AS VARCHAR) +',', '')

			  --'1,2,3,' --> '1,2'
              WHEN CharIndex(CAST(EmpID AS VARCHAR) +',', @ID) <> 0
              THEN REPLACE(@ID, CAST(EmpID AS VARCHAR) +',', '')

			  --',1,2,3' --> '2,3'
              WHEN CharIndex(',' + CAST(EmpID AS VARCHAR), @ID) <> 0
              THEN REPLACE(@ID, ',' + CAST(EmpID AS VARCHAR), '')

			  --'1' --> '2,3'
              WHEN CharIndex(CAST(EmpID AS VARCHAR), @ID) <> 0
              THEN REPLACE(@ID, CAST(EmpID AS VARCHAR), '')
       END
--The above case have to remove the unwanted comma before or after the EmpID in the variable @ID
--If we dont want to use the above cases, use the below line of code to remove unwanted EmpID from the variable @ID
--@ID = COALESCE(REPLACE(@ID, CAST(EmpID AS VARCHAR), ''), @ID)
--But it contains unwanted comma, use the --Option 1 Starts query for remove unwanted comma
FROM   @Temp
WHERE  ',' + @ID + ',' LIKE '%,' + CAST(EmpID AS VARCHAR) + ',%'
AND MsgID  = 2

/*--Option 1 Starts
SELECT SingleSpaceTextColumn = REPLACE( REPLACE( REPLACE( LTrim(RTRIM(@ID)), --Trim the field
       ',,',',|'),                                            --Mark double comma
       '|,',''),                                              --Delete double comma offset by 1
       '|','')                                                --Tidy up
--This removes More than one comma with single comma. But it didn't remove Leading/trailing comma
--The below line of Select Statement removes the Leading/trailing comma if exists
SELECT
       CASE
              WHEN LEFT(@ID, 1)    = ','
              AND    RIGHT(@ID, 1) = ','
              THEN Substring(@ID, 2, LEN(@ID) - 2)
              WHEN LEFT(@ID, 1) = ','
              THEN Substring(@ID, 2, LEN(@ID) - 1)
              WHEN RIGHT(@ID, 1) = ','
              THEN Substring(@ID, 0, LEN(@ID))
              ELSE @ID
       END
--Option 1 Ends
*/


SELECT @ID AS EmpIDHaveToInsert

--Now split the EmpId from the variable @ID and Insert to the table


/*
--Maximum allowed size of the comma delimited varible(@ID) along with comma is 3966
--http://www.kodyaz.com/articles//t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx
DECLARE @Split CHAR(1), @X XML
SET @Split = ','

SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@ID,@Split,'</s><s>') + '</s></root>')

INSERT INTO @Temp
SELECT T.c.value('.','varchar(20)') AS EmpID, 2 AS MsgID
FROM @X.nodes('/root/s') T(c)

SELECT * FROM @Temp
*/

/* This Cte accepts more comma delimited value when comared with the above commented query.*/

DECLARE @InputString VARCHAR(8000)
DECLARE @Seperator CHAR(1)

SELECT @InputString = @ID
SET @Seperator = ','

;WITH Pieces(RowIndex, Start, Stop) AS (
      SELECT 1, 1, CHARINDEX(@Seperator, @InputString)
	  --Set first charIndex of the @Seperator in to the Column-Name Stop
      UNION ALL
      SELECT RowIndex + 1, Stop + 1, CHARINDEX(@Seperator, @InputString, Stop + 1)
      FROM Pieces
      WHERE Stop > 0
    )

	INSERT INTO @Temp
    SELECT --RowIndex, 
		   RTRIM(LTRIM(
           SUBSTRING(@InputString, Start,
           CASE
                  WHEN Stop > 0
                  THEN Stop-Start
                  ELSE 512
           END))) AS EmpID, 2 AS MsgID
    FROM   Pieces
--Note
--MAXRECURSION limited to 32767 -> if exceed it throw exception(The statement terminated. The maximum recursion 32767 has been exhausted before statement completion.)
--If we set MAXRECURSION to 0 by default it will took the maximum recursion value 32767.
--So maximum comma seperated value to be 32766
--It we didn't set, The server-wide default is 100.
--To the input there should be no Leading or Trailing commas
OPTION (MAXRECURSION 4);

SELECT * FROM @Temp