Earlier this week, my team & I were working on financial reports and we developed some giant scripts in order to generate report data. Once we were done with the report, we came to know that we did not implement dynamic sorting in the report. Ooopssss !!!. Now do we need to re-write the query and convert into dynamic query ? 😦 Of course NOT.
What you can actually do is, write a conditional case and make it dynamic sort but it will increase the size of your query depending upon how many conditions you have.
In today’s post I will implement the dynamic sorting using CHOOSE function & CASE Statement. First of all, I will find the column number using CASE Statement and then pass the Column order number to CHOOSE function to actually sort the column. It will actually reduce the size of your query.
Given below is the script which will dynamically sort the query.
Use AdventureWorks2014 GO DECLARE @SortCoulmnName VARCHAR(50) = 'OrderDate'; DECLARE @SortColumnNumber AS INT SET @SortColumnNumber = CASE When @SortCoulmnName='SalesOrderID' THEN 0 WHEN @SortCoulmnName='OrderDate' THEN 1 WHEN @SortCoulmnName='DueDate' THEN 2 WHEN @SortCoulmnName='ShipDate' THEN 3 ELSE 0 END -- By Default, it will sort on first column SELECT SalesOrderID ,OrderDate ,DueDate ,ShipDate ,Status FROM Sales.SalesOrderHeader ORDER BY CHOOSE(@SortColumnNumber,SalesOrderID,OrderDate,DueDate,ShipDate) DESC GO
Let me know if you came across this issue and its solution as well.