Ordering SQL Server results by IN clause

I have a stored procedure which uses the IN clause. In my ASP.NET application, I have a multiline textbox that supplies values to the stored procedure. I want to be able to order by the values as they were entered in the textbox. I found out how to do this easily in mySQL (using FIELD function), but not a SQL Server equivalent.

So my query looks like:

Select * from myTable where item in @item

So I would be passing in values from my application like ‘113113’,’112112′,’114114′ (in an arbitrary order). I want to order the results by that list.

Would a CASE statement be feasible? I wouldn’t know how many items are coming in the textbox data.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

How are you parameterising the IN clause?

As you are on SQL Server 2008 I would pass in a Table Valued Parameter with two columns item and sort_order and join on that instead. Then you can just add an ORDER BY sort_order onto the end.

Method 2

From KM’s comment above…

I know you didn’t state it is comma seperated, but if it was a CSV or even if you have it space seperated you could do the following.

DECLARE @SomeTest varchar(100) --used to hold your values
SET @SomeTest = (SELECT '68,72,103') --just some test data

SELECT 
    LoginID --change to your column names
FROM 
    Login   --change to your source table name
INNER JOIN
(   SELECT 
    * 
    FROM fn_IntegerInList(@SomeTest)
) n
ON 
    n.InListID = Login.LoginID
ORDER BY 
    n.SortOrder

And then create fn_IntegerInList():

CREATE FUNCTION [dbo].[fn_IntegerInList] (@InListString ntext)
RETURNS @tblINList TABLE (InListID int, SortOrder int)
AS
BEGIN
declare @length int
declare @startpos int
declare @ctr int
declare @val nvarchar(50)
declare @subs nvarchar(50)
declare @sort int

set @sort=1
set @startpos = 1
set @ctr = 1
select @length = datalength(@InListString)

while (@ctr <= @length)
begin
   select  @val = substring(@InListString,@ctr,1)

   if @val = N',' 
     begin
        select @subs = substring(@InListString,@startpos,@<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="91f2e5e3bcd1e2e5f0e3e5e1fee2">[email protected]</a>)
        insert into @tblINList values (@subs, @sort)
        set @startpos = @ctr+1
     end
  if @ctr = @length 
      begin
        select @subs = substring(@InListString,@startpos,@<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="482b3c3a65083b3c293a3c38273b">[email protected]</a>)
        insert into @tblINList values (@subs, @sort)
      end
  set @ctr = @ctr +1
  set @sort = @sort + 1
end
RETURN
END

This way your function creates a table that holds a sort order namely, SortOrder and the ID or number you are passing in. You can of course modify this so that you are looking for space rather then , values. Otherwise Martin has the right idea in his answer. Please note in my example I am using one of my tables, so you will need to change the name Login to whatever you are dealing with.

Method 3

the same way you concatenate (‘113113′,’112112′,’114114’) to pass to the sql sentence in the where clausule you can concatenate

order by
case item    
when '113113' then 1
when '112112' then 2
when '114114' then 3
end

to pass to your order by clausule

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0