Building a custom ORDER BY clause from a WHERE IN clause

I ran across a pretty interesting requirement.  Be able to sort something off a CSV list of items.  In this case we have a comma separated string we are using for an IN clause

declare @employeeids varchar(50)

set @employeeids = ‘4213,5321,4124’

declare @orderbyClause nvarchar(MAX)
select @orderbyClause = COALESCE(@orderbyClause, '') + ' when employee_id = ' + convert(varchar, number) + ' then ' + convert(varchar, roworder) from
dbo.iter$ordered_intlist_to_tbl(@employeeids)
select ‘ORDER BY CASE ' + @orderbyClause + ' END'   

We are also using a custom table valued function which turns a CSV into a table and maintains the order using a roworder.

/****** Object:  UserDefinedFunction [dbo].[iter$simple_intlist_to_tbl]    Script Date: 12/08/2009 12:16:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[iter$ordered_intlist_to_tbl] (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number int NOT NULL, roworder int NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int,
           @rowcounter int

   SELECT @pos = 0, @nextpos = 1, @rowcounter = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END – @pos – 1
      INSERT @tbl (number, roworder)
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)), @rowcounter)
      SELECT @pos = @nextpos
      SELECT @rowcounter = @rowcounter + 1
   END
  RETURN
END
GO