comma separated list into stored procedure and order of records

I'm passing a comma separated list into a stored procedure
e.g. exec usp_returnProductsFromIdArray '5,4,1,99'
The comma list is being turned into a table (see usp_IntListToTable below)
Here's the proc that returns products that are in the list
CREATE procedure usp_returnProductsFromIdArray
@.prodIdList varchar(1000) = null
as
CREATE TABLE #prodIdTable (productId BIGINT)
if (@.prodIdList is not null)
EXEC usp_IntListToTable @.prodIdList,'#prodIdTable'
select productId, productTitle from products where productId in
(select productId from #prodIdTable)
drop table #prodIdTable
This works well, except I'd like the order of records returned to follow the
order of the IDs in the comma separated list, they aren't, they are in
ascending order (due to the key on the products table)
/*
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
*/
CREATE PROCEDURE dbo.usp_IntListToTable
@.cslist VARCHAR(8000),
@.tablename SYSNAME AS
BEGIN
DECLARE @.spot SMALLINT, @.str VARCHAR(8000), @.sql VARCHAR(8000)
WHILE @.cslist <> ''
BEGIN
SET @.spot = CHARINDEX(',', @.cslist)
IF @.spot>0
BEGIN
SET @.str = CAST(LEFT(@.cslist, @.spot-1) AS INT)
SET @.cslist = RIGHT(@.cslist, LEN(@.cslist)-@.spot)
END
ELSE
BEGIN
SET @.str = CAST(@.cslist AS INT)
SET @.cslist = ''
END
SET @.sql = 'INSERT INTO '+@.tablename+'
VALUES('+CONVERT(VARCHAR(100),@.str)+')'
EXEC(@.sql)
END
END
GOAdd a new column to the temp table to define the sequence. Increment an
integer value in the WHILE loop and insert that into the sequence
number column. Then do:
SELECT P.productid, P.producttitle
FROM products AS P
JOIN #prodIdTable AS T
ON P.productid = T.productid
ORDER BY T.sequence_no ;
David Portas
SQL Server MVP
--|||It works
Thanks very much!!!
www.xwords.co.uk
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129194249.222211.192330@.o13g2000cwo.googlegroups.com...
> Add a new column to the temp table to define the sequence. Increment an
> integer value in the WHILE loop and insert that into the sequence
> number column. Then do:
> SELECT P.productid, P.producttitle
> FROM products AS P
> JOIN #prodIdTable AS T
> ON P.productid = T.productid
> ORDER BY T.sequence_no ;
> --
> David Portas
> SQL Server MVP
> --
>