CODEWITHSUNDEEP
asp.netsqldatabasesql-server-2008stored-procedures
Andrew
Andrew

Reputation: 2839

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.

Upvotes: 9

Views: 3921

Answers (3)

Saic Siquot
Saic Siquot

Reputation: 6513

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

Upvotes: 2

JonH
JonH

Reputation: 33173

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,@ctr-@startpos)
        insert into @tblINList values (@subs, @sort)
        set @startpos = @ctr+1
     end
  if @ctr = @length 
      begin
        select @subs = substring(@InListString,@startpos,@ctr-@startpos)
        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.

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453707

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.

Upvotes: 5

Related Questions