Parameterize Sorting Stored Procedure

In Data-driven application or website, dynamic sorting is common need. Ideally we should write stored procedure for dynamic sorting.

We found nice solution for above problem. Bellow stored procedure fetch ordered data with passed parameter. There two parameters for dynamic sorting stored procedure. Parameter @sortDirection pass for order direction (asc or desc). Second parameter @sortCol for pass sorting field name.

Code

---

=====================================================-
-- Create date:   18-Nov-2011
-- Description:   Example of Fetch Data With Sorting Parameter
=====================================================
Create PROCEDURE Product_Sorting_Parameter
    -- Add the parameters for the stored procedure here
    @sortDirection as varchar(5),
    @sortCol as varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from

    SET NOCOUNT ON;
    -- Select Query
    SELECT
        [ProductID] ,[Name],[ProductNumber],[Color],[ListPrice],[Size]     
     FROM         [AdventureWorks2008R2].[Production].[Product]
     ORDER BY
     -- Name
     Case WHEN @sortCol = 'Name' and @sortDirection = 'asc' THEN Name end,
     Case WHEN @sortCol = 'Name' and @sortDirection = 'desc' THEN Name end desc,
     -- Size
     Case WHEN @sortCol = 'Size' and @sortDirection = 'asc' THEN Size end,
     Case WHEN @sortCol = 'Size' and @sortDirection = 'desc' THEN Size end desc,
     -- Color
     Case WHEN @sortCol = 'Color' and @sortDirection = 'asc' THEN Color end,
     Case WHEN @sortCol = 'Color' and @sortDirection = 'desc' THEN Color end desc,
     -- Price
     Case WHEN @sortCol = 'Price' and @sortDirection = 'asc' THEN ListPrice end,
     Case WHEN @sortCol = 'Price' and @sortDirection = 'desc' THEN ListPrice end desc
END
GO

Execution

---

exec Product_Sorting_Parameter 'asc', 'Name'

Result

---

I hope this may be you also. If anybody has better query than above query, I am waiting for it.