Return SQL query results in order of input into WHERE clause

Since the order by clause is the only way to ensure result set order, you would have to build an order by clause to match your where clause, like this:

select *
from LoanTable
where LoanNumber in (3,2,4,5,1)
order by case LoanNumber
when 3 then 1
when 2 then 2
when 4 then 3
when 5 then 4
when 1 then 5
else 9999 end

If you're building the where dynamically, this wouldn't be too bad though, as the logic for order by would match at every step.

I would go by creating a Table type to hold the LoanNumber's to be selected. Doing so, the query would look more readable.

DECLARE @LoanNumberTable TABLE
(
  [ID] INT IDENTITY (1,1), 
  [LoanNumber] INT    
);

INSERT INTO @LoanNumberTable VALUES (3), (2), (4), (5), (1);

SELECT LoanTable.* 
FROM LoanTable 
INNER JOIN @LoanNumberTable AS Temp ON LoanTable.LoanNumber = Temp.LoanNumber
ORDER BY Temp.ID;

The construct (3,2,4,5,1) is a set. It contains no order and one cannot ask for an ORDER BY based on it. What you need is to pass in a table valued parameter with two fields, one for LoanNumber and one for desired order (rank). The query by joining the TVP:

SELECT ...
FROM LoanTable
JOIN @tvp t ON LoanTable.LoanNumber = t.LoanNumber
ORDER BY t.Order;

Read more about table valued parameters.

select *
from LoanTable
join (values (3,1), (2,2), (4,3), (5,4), (1,5)) v(Id, Or)
on LoanTable.LoanNumber  = v.ID 
order by v.Or

IF you know your order-expression is numeric, you could do somthing like that:

select *, abs(sign(LoanNumber-3)) as Ord1, abs(sign(LoanNumber-2)) as Ord2, abs(sign(LoanNumber-4)) as Ord3...
from LoanTable
where LoanNumber in (3,2,4,5,1)
ORDER BY Ord1, Ord2, Ord3

By that, you build a difference per Input, get 1 or 0 if it meets your ordercriteria, and order by that.

This kind of thing is maybe only to be used with a small resultset...