Sorting data in a Stored Procedure

[Juice05]

I already have a dynamic sql resolution for this, but I want to get away from using dynamic sql.

I have a search page (aspx) where you can enter a first name, last name, ssn and select a department from a drop down. I am using a stored procedure to return the results. Here is my code and it works fine.

CREATE PROCEDURE dbo.GetClients @FirstName Varchar(20), @LastName Varchar(30), @SSN Varchar(11), @DepartmentID Int, @Order Int

AS

If @DepartmentID <> 0

Select * From Clients
Left Join (Select DepartmentID, DepartmentName From Departments) Departments On Clients.DepartmentID = Departments.DepartmentID 

Where Clients.FirstName Like '%' + @FirstName + '%' And Clients.LastName Like '%' + @LastName + '%' And Clients.SSN Like '%' + @SSN + '%' And Department.DepartmentID = @DepartmentID

Order By 
	 Case When @Order = 0 Then FirstName
	          When @Order = 1 Then LastName
   	          When @Order = 2 Then SSN
	          When @Order = 3 Then DepartmentName
	  End
Else

Select * From Clients
Left Join (Select DepartmentID, DepartmentName From Departments) Departments On Clients.DepartmentID = Departments.DepartmentID 

Where Clients.FirstName Like '%' + @FirstName + '%' And Clients.LastName Like '%' + @LastName + '%' And Clients.SSN Like '%' + @SSN + '%'

Order By 
	 Case When @Order = 0 Then FirstName
	          When @Order = 1 Then LastName
   	          When @Order = 2 Then SSN
	          When @Order = 3 Then DepartmentName
	  End

GO

It also accepts an Integer to determine what field to use when sorting. What I can't seem to figure out is how to pass in a value so that my SP knows to sort Asc or Desc.

 

[vongrunt]

AFAIK unless you have SQL2005 or want to use dynamic SQL this requires temp table.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLDenis]

or additional if stetament(s) ;-(

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Juice05]

vongrunt,

What is "AFAIK"?

What in SQL 2005 will address issues like this?

SQLDenis,

Yeah, I was afraid of more if statements.

 

[SQLDenis]

AFAIK = As Far As I Know

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> What in SQL 2005 will address issues like this?

Generally speaking, changing sort direction without dynamic SQL is tricky... except for sort over numeric columns. Then you simply multiply value with 1 or -1 and perform ORDER BY that value ASC.

SQL2005 has ranking functions including ROW_NUMBER() so...

Personally I wouldn't do the same with SQL2000 because equivalent ranking code requires either ugly joins or temp table. Too much trouble for such apparently simple issue.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]