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
=====================================================-
-- 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'
