SQL SERVER Stored Procedure (SP): Parameterized ORDER BY without Dynamic-SQL - Example 2 - with ASC/DESC by Column abilities

SQL Server Stored-Procedure performing a parameterized ORDER BY operation, including ASC/DESC Sort options, without using Dynamic-SQL

NOTE: This extends the previous parameterized ORDER BY without using Dynamic-SQL example. An extension of the previous technique, this version allows for column-level ascending/descending preferences for sort direction. A very common requirement, and GUI paradigm, is to allow users to simply "flip" the order from ASC to DESC by clicking on a column-heading in a grid or similar data-control. This procedure introduces per-column sort-direction selector abilities to the parameter-driven ORDER BY technique.

The Microsoft SQL-Server Transact-SQL (T-SQL) stored procedure (SP) or query presented here can accomplish an interesting task: performing a parameterized ORDER BY without using Dynamic-SQL to do it, and even allow the ORDER BY to, by column, sort data in ascending or descending order. This method builds on several other examples I have provided, on this blog, demonstrating various SET-BASED algorithms as solutions to interesting challenges without using Dynamic-SQL. This method of performing a ORDER BY on one or more columns, as specified by parameters/directives, is very flexible though it has the drawback of not executing as quickly as if we knew ahead of time (when writing our SQL code) what column(s) an ORDER BY was to act on.

DISCUSSION: 

Start by reading my comments from the first example source code of the ORDER BY variable-column-list without Dynamic-SQL for a bit of background information. This technique can be very helpful in avoiding dynamically created SQL, or "built up" SELECT statements in a GUI/application, as well as avoiding backend dynamic-SQL for the same. This is one way to enable your user-interfaces with what can be described as user-selectable or user-directed dynamic ORDER BY operations without the underlying SQL code being based on dynamic-SQL or UI-built-up-SQL.

Although there are various client-side JavaScript control-sets and such available, there are times when you really want, or need, the data from the database to be returned in a desired sort order without further client-side code or intervention. Depending on the size of your data-sets, the method presented herein may provide you with a way to provide rich data-sorting feaures to your front-end applications without the need for dynamic-SQL.

I put together this procedural example that demonstrates how you can perform ORDER BY operations dynamically via run-time parameters, without dynamic-SQL. Not only does this example demonstrate how I can, at execution time, force the ORDER BY operation to vary what column is used to group information by, but how I can also have the ORDER BY include a user-select-able ordering that includes one column, two columns, three columns, etc. PLUS ordering in ASCENDING or DESCENDING order (by column).

You will notice how, essentially, the CASE statements used within the ORDER BY clause have simply been doubled up, with the first in each pair being the ASC order logic, and the second being the DESC. Thanks to how the ORDER BY clause interprets NULL values, the CASE statement execution-branch that is not being used will result in a NULL, and have no effect on the sort operation.

This procedure was originally tested against the AdventureWorks sample databases in SQL-Server 2005 and SQL-Server 2008.

SQL-Server Stored Procedure (SP) Source Code

--********************************************************************************
--This source code is Copyright (c) 2007-2017
--     Author: Mike Eberhart
--
--I hereby release this code under the terms of the MIT License (for freeware).
--
--Permission is hereby granted, free of charge, to any person obtaining a copy
--of this software and associated documentation files (the "Software"), to deal
--in the Software without restriction, including without limitation the rights
--to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
--copies of the Software, and to permit persons to whom the Software is
--furnished to do so, subject to the following conditions:
--
--The above copyright notice and this permission notice shall be included in
--all copies or substantial portions of the Software.
--
--THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
--IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
--FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
--AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
--LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
--OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
--THE SOFTWARE.
--********************************************************************************
--**********************************************************************************************
-- BEGIN: NON-DYNAMIC-SQL with DYNAMIC ORDER-BY (+ ASC/DESC) at run-time via Parameters #2.
--
-- This procedure builds on the prior example by adding the abiity to, at a column-level,
-- specify whether to sort in ascending or descending order at run-time, through the additional
-- CASE statement logic in the ORDER BY clause.
--**********************************************************************************************

--**********************************************************************************************
-- Again using the AdventureWorks database's Production.Product Table for this example.
-- I will refer to the various Columns I want to (potentially) ORDER BY as columns 1 through 3,
-- with the values being assigned as follows:
-- 1 = Name
-- 2 = ProductNumber
-- 3 = ListPrice
--
-- Each sort-column parameter now has an accompanying ASC/DESC indicator (pass 'A' or 'D' in).
--**********************************************************************************************
CREATE PROCEDURE uspProducts_ReturnProductsInDesiredSortOrder2
 @SortColumn1  TINYINT,
 @Col1AscDesc  CHAR = 'A',
 @SortColumn2  TINYINT,
 @Col2AscDesc  CHAR = 'A',
 @SortColumn3  TINYINT,
 @Col3AscDesc  CHAR = 'A'
AS
BEGIN
 SELECT
  P.Name,
  P.ProductNumber,
  P.ListPrice,
  P.Color,
  P.DaysToManufacture,
  P.StandardCost
 FROM 
  Production.Product AS P
 ORDER BY 
  --to obtain proper ascending order, we must LEFT-PAD any numerics, and
  --all fields we ORDER BY must be seen by SQL-Server as the same DataType,
  --which is why we CONVERT / PAD all the fields to the same length / type.
  --In THIS example, our greatest-common-denominator is a VARCHAR(50) column.
  (CASE
   WHEN @Col1AscDesc = 'A' THEN
    CASE
     WHEN @SortColumn1 = 1 THEN CONVERT(VARCHAR(50), P.Name)
     WHEN @SortColumn1 = 2 THEN CONVERT(VARCHAR(50), P.ProductNumber)
     WHEN @SortColumn1 = 3 THEN 
      dbo.[udfGetMoneyAsPaddedString](P.ListPrice, 50, '0', DEFAULT)
     ELSE NULL
    END
   ELSE NULL
   END),
  (CASE
   WHEN @Col1AscDesc = 'D' THEN
    CASE
     WHEN @SortColumn1 = 1 THEN CONVERT(VARCHAR(50), P.Name)
     WHEN @SortColumn1 = 2 THEN CONVERT(VARCHAR(50), P.ProductNumber)
     WHEN @SortColumn1 = 3 THEN 
      dbo.[udfGetMoneyAsPaddedString](P.ListPrice, 50, '0', DEFAULT)
     ELSE NULL
    END
   ELSE NULL
   END) DESC,
  (CASE
   WHEN @Col2AscDesc = 'A' THEN
    CASE
     WHEN @SortColumn2 = 1 THEN CONVERT(VARCHAR(50), P.Name)
     WHEN @SortColumn2 = 2 THEN CONVERT(VARCHAR(50), P.ProductNumber)
     WHEN @SortColumn2 = 3 THEN 
      dbo.[udfGetMoneyAsPaddedString](P.ListPrice, 50, '0', DEFAULT)
     ELSE NULL
    END
   ELSE NULL
   END),
  (CASE
   WHEN @Col2AscDesc = 'D' THEN
    CASE
     WHEN @SortColumn2 = 1 THEN CONVERT(VARCHAR(50), P.Name)
     WHEN @SortColumn2 = 2 THEN CONVERT(VARCHAR(50), P.ProductNumber)
     WHEN @SortColumn2 = 3 THEN 
      dbo.[udfGetMoneyAsPaddedString](P.ListPrice, 50, '0', DEFAULT)
     ELSE NULL
    END
   ELSE NULL
   END) DESC,
  (CASE
   WHEN @Col3AscDesc = 'A' THEN
    CASE
     WHEN @SortColumn3 = 1 THEN CONVERT(VARCHAR(50), P.Name)
     WHEN @SortColumn3 = 2 THEN CONVERT(VARCHAR(50), P.ProductNumber)
     WHEN @SortColumn3 = 3 THEN 
      dbo.[udfGetMoneyAsPaddedString](P.ListPrice, 50, '0', DEFAULT)
     ELSE NULL
    END
   ELSE NULL
   END),
  (CASE
   WHEN @Col3AscDesc = 'D' THEN
    CASE
     WHEN @SortColumn3 = 1 THEN CONVERT(VARCHAR(50), P.Name)
     WHEN @SortColumn3 = 2 THEN CONVERT(VARCHAR(50), P.ProductNumber)
     WHEN @SortColumn3 = 3 THEN 
      dbo.[udfGetMoneyAsPaddedString](P.ListPrice, 50, '0', DEFAULT)
     ELSE NULL
    END
   ELSE NULL
   END) DESC

END --Procedure

--**********************************************************************************************
--TESTING / EXAMPLE EXECUTIONS
--**********************************************************************************************

--Sort by ListPrice ASC
EXEC uspProducts_ReturnProductsInDesiredSortOrder2 3, 'A', NULL, NULL, NULL, NULL

--Sort by ListPrice DESC, then ProductNumber ASC
EXEC uspProducts_ReturnProductsInDesiredSortOrder2 3, 'D', 2, 'A', NULL, NULL


--**********************************************************************************************
-- END: NON-DYNAMIC-SQL with DYNAMIC ORDER-BY (+ ASC/DESC) at run-time via Parameters #2.
--**********************************************************************************************

Continue to read this Software Development and Technology Blog for computer programming, software development, and technology Techniques, How-To's, Fixes, Reviews, and News — focused on Dart Language, SQL Server, Nvidia CUDA, VMware, TypeScript, SVG, other technology tips and how-to's, and my varied political and economic opinions.