Using a dynamic variable in the Order By clause.

Last Post 26 Oct 2011 06:31 AM by gizzy. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Resolved
gizzy
New Member
New Member

--
25 Oct 2011 10:52 AM

I have been given the task to write a SQL stored proc that will carry variables in from an ASP page. The initial page will load with no order by however the end user will be given the option to sort by column names and choose asc or desc.

When I hardcode a case statement in the Order by it works as it should. The problem several things:
1) the table can have 20+ rows to choose from on a sort/ asc/desc
2) this is going to be a standard format for other webpages using this same basic SQL statement.

So hardcoding is the least of my choices. Any help is appreciated.

DECLARE @OR Sysname, @Dir varchar(4) 

SET @OR = ''
SET @DIR = ''


SELECT col1, col2, col3,...etc
FROM [database].[dbo].[table]
WHERE DateDiff(d,date col] ,GetDate())=0 

ORDER BY 
                                 (will accept variable but NOT asc vs desc)

                              CASE @Dir WHEN 'DESC' THEN  
                                             CASE WHEN @OR '' THEN left(@OR,len(@OR-1))
                                           END 
                                         END 
                                 DESC;



                             ( This works as it should including asc vs desc)
                           CASE @DESC 
                                WHEN 'DESC' THEN 
                                     CASE @OR WHEN 'col1'THEN col1
                                    END 
                                END 
                            DESC,
    
                         CASE @DESC 
                              WHEN 'DESC' THEN 
                                    CASE @OR WHEN 'col2'THEN col2
                                     END 
                               END 
                           DESC ; 

   continuing with all column names.

gunneyk
New Member
New Member

--
25 Oct 2011 02:25 PM
Erland has a bunch of good info on this specific topic here: http://www.sommarskog.se/dyn-search.html

But he has other good stuff you may be interested in as well:

http://www.sommarskog.se/index.html
gizzy
New Member
New Member

--
26 Oct 2011 06:31 AM
Thank you for the website. First it helped me change my way of doing the query and it now works as it should! And secondly this is just a really great site, there is a lot of valuable information!

Many thanx again 


Acceptable Use Policy
---