create proc spGetCustomersByAlphaOrder
@firstName nvarchar(50),
@lastName nvarchar(50),
@email nvarchar(50)
as
begin
   select * from Customers
   where (firstName like @firstName+'%' or @firstName = null)and
         (lastName like @lastName+'%' or @lastName = null)and
         (email like @email+'%' or @email = null)

   order by ???? I WANT TO ORDER BY SAME COLUMN AS THE "WHERE-SEARCH" 

end

Please let me know how to integrate an "Order-By-Possibility"

Many thanks in advance!
landau

create proc spGetCustomersByAlphaOrder
@firstName nvarchar(50),
@lastName nvarchar(50),
@email nvarchar(50)
as
begin
   select * from Customers
   where (firstName like @firstName+'%' or @firstName is null)and
         (lastName like @lastName+'%' or @lastName is null)and
         (email like @email+'%' or @email is null)

   order by case when @firstName > '' then firstName else 1 end,
            case when @lastName > '' then lastName else 1 end,
            case when @email  > '' then email else 1 end 

end

quote:

---

Originally posted by landau66

Hi
and thank you for your answers. It really helped me a lot.

I still have a question: Why is the solution from visakh16 working? The ORDER BY part uses CASE WHEN ELSE. I dont understand why it Needs the ELSE 1 part - what does it do?

case when @firstName > '' then firstName else 1 end

Thank you very much in advance
landau

---