Ordering SQL Results based on Input Params

Question

In conjunction with the fn_split function, I'm returning a list of results from a table based on comma separated values.

The Stored Procedure T-SQL is as follows:

SELECT ProductCode, ProductDesc, ImageAsset, PriceEuros, PriceGBP, PriceDollars,  
replace([FileName],' ','\_') as [filename],  
ID as FileID, weight  
from Products  
LEFT OUTER JOIN Assets on Assets.ID = Products.ImageAsset  
where ProductCode COLLATE DATABASE_DEFAULT IN  
(select [value] from fn\_split(@txt,','))  
and showOnWeb = 1

I pass in to the @txt param the following (as an example):

ABC001,ABC009,ABC098,ABC877,ABC723

This all works fine, however the results are not returned in any particular order - I need the products returning in the 'SAME ORDER' as the input param.

Unfortunately this is a live site with a built schema, so I can't change anything on it (but I wish I could) - otherwise I would make it more sensible.

Answer 1

If all of the references that are passed in on the @txt param are unique you could use CharIndex to find their position within the param e.g.

order by charindex(ProductCode, @txt)

Answer 2

In the stored procedure, I would create a table which has a numeric key which is the PK for the temp table and set to auto-increment. Then, I would insert the results of fn_split into that table, so that you have the parameters as they are ordered in the list (the auto-increment will take care of that).

Then, join the result set to this temp table, ordering by the numeric key.

If the entries in the parameter list are not unique, then after inserting the records into the temp table, I would delete any duplicates, except for the record where the id is the minimum for that particular parameter.