[thelist] Ordering a recordset by an array
Joshua Olson
joshua at waetech.com
Tue Oct 14 15:06:01 CDT 2003
----- Original Message -----
From: "Ben Gustafson" <Ben_Gustafson at lionbridge.com>
To: <thelist at lists.evolt.org>
Sent: Tuesday, October 14, 2003 3:16 PM
Subject: Re: [thelist] Ordering a recordset by an array
> Thing o' beauty, Rudy. For the edification of others, here's the function
I
> created, based on Rudy's SQL, for returning a recordset ordered in the
order
> of the IDs in the array passed in as an argument:
Rudy, et al,
Here's an sp to do about the same thing. It takes the list, populates a
temp table, the joins against it to get the ordering. It doesn't choke on
duplicates... you just get the record twice:
CREATE PROCEDURE select_list_in_order
@thelist varchar(1000) = NULL
AS
DECLARE @pos int
DECLARE @value varchar(20)
CREATE TABLE #temp ( id int null, rank int identity)
Set @thelist = @thelist + ','
WHILE PatIndex('%,%' , @thelist) <> 0
BEGIN
SET @pos = PatIndex('%,%' , @thelist)
SET @value = Left(@thelist, @pos - 1)
SET @thelist = Stuff(@thelist, 1, @pos, '')
INSERT INTO #temp (id) VALUES (Convert(int, @value))
END
SELECT *
FROM thetable
INNER JOIN #temp
ON #temp.id = thetable.id
ORDER BY #temp.rank
DROP TABLE #temp
GO
Usage: EXEC select_list_in_order '1,5,10,12,5,8,2,1'
<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com
706.210.0168
More information about the thelist
mailing list