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