SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SP with variables and ORDER BY
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

landau66
Yak Posting Veteran

Austria
61 Posts

Posted - 12/29/2013 :  06:50:31  Show Profile  Reply with Quote
Hi everyone,

I work on a little program. I want to achieve the following:

A stored procedure which selects records from one table. I will only use one of the variables - never more than one. I want to use "order by" to order the result by the same column alphabetically as I use for search Input.


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


visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/29/2013 :  12:51:15  Show Profile  Reply with Quote
your procedure should be like this

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 12/29/2013 :  21:16:32  Show Profile  Reply with Quote

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 @firstName + firstName,
            @lastName + lastName,
            @email + email 

end
Go to Top of Page

landau66
Yak Posting Veteran

Austria
61 Posts

Posted - 01/07/2014 :  05:15:43  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/07/2014 :  05:26:37  Show Profile  Reply with Quote
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


it bypasses the cases where particular filter is not selected to consider all values as 1.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000