February 26, 2002 at 3:01 pm
On a website that I have, I allow the users to sort column data by clicking on the column heading. Then they can sort Ascending or Descending. The only way I have found to do this is to create the SQL statement dynamically and then execute it. I have created a stored procedure that has parameters to tell me what colums and direction to sort then I do something like:
Create Procedure csp_GetName
@ParamColumn varchar(50) = 'LName',
@ParamDirection varchar(4) = 'ASC'
AS
BEGIN
SET NOCOUNT ON
DECLARE @sSQL varchar(1000)
@sSQL= 'SELECT LName, FName, Address1, City, State, Zip FROM t_User WHERE t_User_pk = 35 ORDER BY '+@ParamColumn+' '+@ParamDirection
EXEC @sSQL
SET NOCOUNT OFF
END
How does anyone achieve the same results?
David Petersen
dipetersen.com
February 26, 2002 at 3:46 pm
Using CASE in the ORDER BY clause it's possible to select the columns:
USE Northwind
GO
DECLARE @sort_column varchar(10)
DECLARE @order char(1)
SET @sort_column = 'CustomerID'
SET @order = 'A'
SELECT CustomerID, ContactName
FROM Customers
ORDER BY
CASE WHEN @sort_column = 'CustomerID' THEN CustomerID
ELSE ContactName
END
I didn't play with it enough to determine if the direction could be dynamically determined (couldn't do it quickly with CASE), but at worst, you're looking at two stored procedures, one for each direction.
One thing you might want to look at is the execution plans and whether or not they are being recompiled, since you're doing a sort on different columns.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 26, 2002 at 4:34 pm
I have tried and found no way to do order direction as of yet. You may if the code is short just double the code with an option for ASC and one for DESC and and if that checks which to run.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 26, 2002 at 10:40 pm
Thank you for your suggestions. I will use the CASE in the order by and then I will have two options - one for ASC and one for DESC.
Thank you for your help.
David Petersen
dipetersen.com
February 27, 2002 at 4:16 am
Here is an example using the pubs database that can handle ASC and DESC in one query:
DECLARE @OrderBy varchar(10), @Sequence varchar(4)
SET @OrderBy = 'au_lname'
SET @Sequence = 'DESC'
SELECT *
FROM authors
ORDER BY CASE @OrderBy + @Sequence WHEN 'au_lnameASC' THEN au_lname WHEN 'au_fnameASC' THEN au_fname END ASC,
CASE @OrderBy + @Sequence WHEN 'au_lnameDESC' THEN au_lname WHEN 'au_fnameDESC' THEN au_fname END DESC,
CASE @OrderBy + @Sequence WHEN 'au_lnameASC' THEN au_fname END ASC,
CASE @OrderBy + @Sequence WHEN 'au_lnameDESC' THEN au_fname END DESC
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 27, 2002 at 4:33 am
Wow I missed that one as I was thinking that if your case output no column you would be left with ASC, aulname DESC, ASC, aufname DESC which will bark "Incorrect syntax near the keyword 'ASC'". Did not think to try with ASC outside as such. Thanks Robert something new to add today.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 27, 2002 at 8:30 am
Robert, thanks for the idea. I played with it and it works, I just don't understand how! Looking at the results, you would think that if the CASE didn't return a value, then the SQL would be :
ORDER BY au_lname ASC,DESC,ASC,etc..
That should produce an error but it doesn't. Then only thing that I can think of is that the CASE returns a NULL value so the ORDER BY NULL DESC is ignored.
Am I way off track here?
Thanks again for your help. I never would have thought of this solution.
David Petersen
dipetersen.com
February 27, 2002 at 8:38 am
I copied the CASE statements and put a SELECT in front of them and removed the DESC and ASC at the end.
Without reading up on the subject, I'd have to say that SQL Server treats everything between CASE and END as the column name to use in the ORDER BY and since this is in the ORDER BY clause SQL Server will allow a ASC or DESC keyword after each column name. In our case each column name stops at the END keyword and so we can simply add the ASC or DESC after that.
I would come to the same conlcusion as you in that since the column name is NULL in some cases SQL Server simply discards the ASC or DESC that follows the non qualifying column name and continues.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 27, 2002 at 6:26 pm
Another approach would be to return the data as xml. Then, using client side script, resort the data. This way there is only one call to the db and the sorting is done on the client.
February 28, 2002 at 9:46 am
Jon's approach is a great one for reducing the load on the server.
One question, when you do this, does the stored procedure recompile? I am curious if the CASE is invalidating the query plan. Or if the old query plan is being used.
Steve Jones
March 3, 2002 at 9:07 pm
I would think that even if the old execution plan was being reused for whatever reason, this is a prime candidate for that little used RECOMPILE option upon stored procedure creation. Since the possibility of changes in the sort step are pretty high, no point going through the lookup process to match and try and retrieve an execution plan.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply