I am at a new position and I am being told to implement a stored procedure that will accept a list of user id's and update a flag. When I suggested the use of a table value parameter (array emulation) I was told to implement as demonstrated below as this is how its been done before. I'm not a DBA but I am a full stack developer and this smells funny to me.

FYI- the user ids are implemented as type int

CREATE PROCEDURE [dbo].[UpdateUsers]       
 @rgIDs varbinary(max)      -- contains several ids
AS      
 DECLARE @tblTmp TABLE (ID int PRIMARY KEY)      
      
 DECLARE @ich int, @cch int, @ID int      
 SET @cch = DATALENGTH (@rgIDs)      
 SET @ich = 1   

 WHILE (@ich < @cch)      
 BEGIN      
  SET @ID = SUBSTRING (@rgIDs, @ich, 4) 
       
  UPDATE dbo.Users u 
  SET isUpdated = 1
  WHERE u.ID = @ID

  SET @ich = @ich + 4      
 END      

Doesn't a table valued type work better here? More readable, performance, less error prone, etc...

I am at a new position and I am being told to implement a stored procedure that will accept a list of user ids and update a flag.

When I suggested the use of a table value parameter (array emulation), I was told to implement as demonstrated below, as this is how it's been done before. I'm not a DBA but I am a full stack developer and this smells funny to me.

FYI- the user ids are implemented as type int

CREATE PROCEDURE [dbo].[UpdateUsers]       
 @rgIDs varbinary(max)      -- contains several ids
AS      
 DECLARE @tblTmp TABLE (ID int PRIMARY KEY)      
      
 DECLARE @ich int, @cch int, @ID int      
 SET @cch = DATALENGTH (@rgIDs)      
 SET @ich = 1   

 WHILE (@ich < @cch)      
 BEGIN      
  SET @ID = SUBSTRING (@rgIDs, @ich, 4) 
       
  UPDATE dbo.Users u 
  SET isUpdated = 1
  WHERE u.ID = @ID

  SET @ich = @ich + 4      
 END      

Doesn't a table valued type work better here? More readable, performance, less error prone, etc...?

I will be using SQL Server 2012 or 2014, I believe. Definitely > 2008.