SQL Server dynamic sorting on multiple columns

Question

I have a stored procedure which uses dynamic sorting, 2 parameters determine the sorting - column: @SortIndex and sort direction: @SortDirection

relevant code:

...
ROW_NUMBER() OVER
            (
            ORDER BY
            --  string order by
            CASE @SortDirection
                  WHEN 'ASC'  THEN
                    CASE @SortIndex
                      WHEN 1 THEN SKU
                      WHEN 2 THEN BrandName
                      WHEN 3 THEN ItemName
                    END            
                END ASC,
                CASE @SortDirection
                  WHEN 'DESC' THEN
                    CASE @SortIndex
                      WHEN 1 THEN SKU
                      WHEN 2 THEN BrandName
                      WHEN 3 THEN ItemName
                    END
                END DESC,

This sorts on single columns, but I want to sort on BrandName ASC, ItemName ASC when @SortIndex is 2.

I'd recommend Dynamic SQL (building up the query in a string and executing it). The complex sorting can quickly cause many CASE structures to cover it. — Allan S. Hansen
– Allan S. Hansen, Commented Jul 23, 2014 at 16:55
Be careful if you go this path. You have strings that look like parameters. You need to be careful about sql injection and use parameterized dynamic sql. — Sean Lange
– Sean Lange, Commented Jul 23, 2014 at 18:18
@KevinCook - I'm slightly embarrassed to report that your solution was basically correct! All I had to do was add WHEN 2 THEN BrandName + ',' + ItemName and it worked. If you want to flesh out your comment & add it as an answer I'll mark it as correct. — markpsmith
– markpsmith, Commented Jul 24, 2014 at 9:46

C.J. · Accepted Answer · 2014-07-23 17:04:51Z

3

If you cannot use Dynamic SQL, the only way is to list all the possible combination for ASC and DESC

For example:

ORDER By
CASE WHEN @SortIndex = '1' AND @SortDirection = 'ASC' THEN SKU END,
CASE WHEN @SortIndex = '1' AND @SortDirection = 'DESC' THEN SKU END DESC,
CASE WHEN @SortIndex = '2' AND @SortDirection = 'ASC' THEN BrandName END,
CASE WHEN @SortIndex = '2' AND @SortDirection = 'DESC' THEN BrandName END DESC,
--and so on...

answered Jul 23, 2014 at 17:04

C.J.

3,5679 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kevin Cook · Accepted Answer · 2014-07-24 13:26:30Z

ROW_NUMBER() OVER
            (
            ORDER BY
            --  string order by
            CASE @SortDirection
                  WHEN 'ASC'  THEN
                    CASE @SortIndex
                      WHEN 1 THEN SKU
                      WHEN 2 THEN BrandName + ',' + ItemName 
                      WHEN 3 THEN ItemName
                    END            
                END ASC,
                CASE @SortDirection
                  WHEN 'DESC' THEN
                    CASE @SortIndex
                      WHEN 1 THEN SKU
                      WHEN 2 THEN BrandName + ',' + ItemName
                      WHEN 3 THEN ItemName
                    END
                END DESC,

Use Brandname + ItemName in the When 2 Clause and to have both fields be used in the sort.

halfzebra · Accepted Answer · 2015-10-18 22:55:12Z

-2

A sample for generality....By K.AryaeeMoeen

---------------------------------------------------
SELECT 1 AS Num, '2015-06-22' AS Datex INTO Arya
INSERT INTO Arya 
SELECT 2, '2015-08-17' UNION SELECT 3, '2015-07-14'
---------------------------------------------------
Now, Dynamic sorting(Base on Datex Field) in a SELECTION ....
---------------------------------------------------
SELECT Num, Date1 FROM ARYA, (SELECT -1 as e union Select 1 as e) a
WHERE a.e=-1  --(OR a.e=1) For Dynamic Sort
ORDER BY DATEDIFF(DAY, '2000-01-01', Arya.Datex)*sign(a.e)
---------------------------------------------------

edited Oct 18, 2015 at 22:55

halfzebra

6,8154 gold badges34 silver badges47 bronze badges

answered Oct 18, 2015 at 21:35

Keyvan AryaeeMoeen

11

Collectives™ on Stack Overflow

SQL Server dynamic sorting on multiple columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related