parameter in Order By clause? 1

[chris123321]

A user has a drop-down list in which he can decide how to Group By a report. And the Group-By of the report depends on the Order By clause of the stored procedure. So for each of the different selections in the dropdownlist, the report will look different.

The best solution I found is using a case statement from:

http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html

DECLARE @col VARCHAR(9) 
SET @col = 'firstname' 
 
IF @col IN ('firstname', 'email') 
    SELECT * FROM blat 
        ORDER BY CASE @col 
            WHEN 'firstname' THEN firstname 
            WHEN 'email' THEN email 
        END 
ELSE 
    SELECT * FROM blat 
GO

Any other suggestions?

 

[gmmastros]

UDF's can be performance killers. It's best to figure out why the derived table isn't working.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chris123321]

Worked perfectly, George..

Thanks for your help. Using the derived table the performance time of the sproc was 2 seconds.