July 14, 2009 at 11:39 am
I have a query that uses a function with a parameter to pass several Employee ID's to my stored procedures.
Users enter several employee ID's in a search box and then click search that all works fine. However when the results are returned they are in order by ID. The user would like the results to be in the order that they entered the ID's in on the search page.
For instance jane doe goes to the search page and enters id's 5,3,4,12,9,6,14
into the search box that is passed to my @Id parameter.
They would like the results returned in the order they entered the parameters - 5,3,4,12,9,6,14.
Here's a VERY simple sample of my query
Declare @ID as VarChar (1000)
Select ID,Name from employees
where ID in (select * from dbo.splitstring(@ID,','))
I am not sure how to get it to order by what the actual values of the parameter is.
July 14, 2009 at 12:21 pm
Might be ugly, but you could use ORDER BY CASE statements and dynamic SQL to do it.
July 14, 2009 at 1:07 pm
This is a concept but could you load the sting into a temp table using your function. Create the temp table with an identity column called sortid
create table #test1 (sortid int identity (1,1), id int)
insert #test1 select ID from dbo.splitstring(@ID,',')
Now do your select:
Select ID,Name from employees e
inner join #test1 t on t.id = e.id
order by t.sortid
Not tested just an idea.
July 14, 2009 at 2:23 pm
I would say that LeeM's suggestion is the only way to make it happen. Since the ID's can be in any order you need some kind of indexer to sort by and the temp table or table variable with an identity column would work best in SQL Server. Just one bug in the code LeeM provided. You need to change the insert to include the column:
insert #test1 (id) select ID from dbo.splitstring(@ID,',')
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 15, 2009 at 6:00 am
Ok thanks guys I'll try lee's idea
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply