SQL SERVER Stored Procedure (SP): Parameterized ORDER BY without Dynamic-SQL - Example 1

SQL Server Stored-Procedure performing a parameterized ORDER BY operation without using Dynamic-SQL

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 so. This method builds on some other examples I have provided demonstrating various SET-BASED algorithms as solutions to interesting challenges without using Dynamic-SQL. This method of performing an 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.

Avoidance of Dynamic-SQL is primarily motivated by security considerations, and this procedure does not use any dynamic-SQL to perform what would otherwise be considered a "dynamic ORDER BY" operation, where the column(s) for sorting on are variable in nature. SQL-injection exploits can be avoided completely by not using dynamic-SQL. This is particularly important for applications that provide the "public" with access to a database via web-interfaces and the like. And, very often, data-selection and sorting options are provided to the public (or are a desired application feature) that would typically make dynamic-SQL a requirement.

NOTE: This query / procedure is extended to include provisions for sort-direction (ASC/DESC) in my followup example source code for a parameterized ORDER BY without using Dynamic-SQL example - with ASC / DESC by column option.

DISCUSSION: 

There are a proliferation of desktop and web-based applications that present their users with data in some form of sortable result set, where the only obvious solution to the developer that designed and implemented the sorting options (whether a SQL developer, desktop applications developer, or web application developer) is to use dynamically created SQL, or "built up" SELECT statements in the GUI/application. This is one area within applications that I repeatedly see all sorts of openings for SQL-injection attacks and other security issues, as I have seen the most built-up-SQL being passed to a database engine, or the most dynamic-SQL in back-end stored procedures that interact with the application or web GUIs that strive to present their users with robust client-side data sorting options.

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 features 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-selectable ordering that includes one column, two columns, three columns, etc.

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 specified at run-time via Parameters #1.
-- Allow a user to perform an ORDER BY, up to 5-column values worth of sorting deep
-- (and as few as *zero* sort columns), with the column(s) involved in the sorting determined
-- at runtime.
--**********************************************************************************************

--**********************************************************************************************
-- We're using the AdventureWorks database's Production.Product Table for this example.
-- Refer to the various Columns to (potentialy) ORDER BY as columns 1 through 5,
-- with the values being equated to a column as follows:
-- 1 = Name
-- 2 = ProductNumber
-- 3 = Color
-- 4 = ListPrice
-- 5 = DaysToManufacture
--
-- We could just as easily passed in column-names, and acted on those in our CASE statements, 
-- but Numbers were chosen to represent columns here for brevity / simplicity.
-- DO NOT CONFUSE the numeric representation of Columns with the Parameter Numbers.
--
-- NOTE: This example sorts all columns in default (i.e., ASC) order.
-- I address altering the ASC/DESC ORDER BY, on a column-by-column basis, in my next example.
--**********************************************************************************************
CREATE PROCEDURE uspProducts_ReturnProductsInDesiredSortOrder
 @SortColumn1 TINYINT,
 @SortColumn2 TINYINT,
 @SortColumn3 TINYINT,
 @SortColumn4 TINYINT,
 @SortColumn5 TINYINT
AS
BEGIN
 SELECT
  P.Name,
  P.ProductNumber,
  P.Color,
  P.ListPrice,
  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 @SortColumn1 = 1 THEN CONVERT(VARCHAR(50), P.Name)
     WHEN @SortColumn1 = 2 THEN CONVERT(VARCHAR(50), P.ProductNumber)
     WHEN @SortColumn1 = 3 THEN CONVERT(VARCHAR(50), P.Color)
     WHEN @SortColumn1 = 4 THEN 
     dbo.[udfGetMoneyAsPaddedString](P.ListPrice, 50, '0', DEFAULT)
     WHEN @SortColumn1 = 5 THEN
     dbo.[udfGetIntegerAsPaddedString](P.DaysToManufacture, 50, '0')
     ELSE NULL
   END),
  (CASE WHEN @SortColumn2 = 1 THEN CONVERT(VARCHAR(50), P.Name)
     WHEN @SortColumn2 = 2 THEN CONVERT(VARCHAR(50), P.ProductNumber)
     WHEN @SortColumn2 = 3 THEN CONVERT(VARCHAR(50), P.Color)
     WHEN @SortColumn2 = 4 THEN 
     dbo.[udfGetMoneyAsPaddedString](P.ListPrice, 50, '0', DEFAULT)
     WHEN @SortColumn2 = 5 THEN
     dbo.[udfGetIntegerAsPaddedString](P.DaysToManufacture, 50, '0')
     ELSE NULL
   END),
  (CASE WHEN @SortColumn3 = 1 THEN CONVERT(VARCHAR(50), P.Name)
     WHEN @SortColumn3 = 2 THEN CONVERT(VARCHAR(50), P.ProductNumber)
     WHEN @SortColumn3 = 3 THEN CONVERT(VARCHAR(50), P.Color)
     WHEN @SortColumn3 = 4 THEN 
     dbo.[udfGetMoneyAsPaddedString](P.ListPrice, 50, '0', DEFAULT)
     WHEN @SortColumn3 = 5 THEN
     dbo.[udfGetIntegerAsPaddedString](P.DaysToManufacture, 50, '0')
     ELSE NULL
   END),
  (CASE WHEN @SortColumn4 = 1 THEN CONVERT(VARCHAR(50), P.Name)
     WHEN @SortColumn4 = 2 THEN CONVERT(VARCHAR(50), P.ProductNumber)
     WHEN @SortColumn4 = 3 THEN CONVERT(VARCHAR(50), P.Color)
     WHEN @SortColumn4 = 4 THEN 
     dbo.[udfGetMoneyAsPaddedString](P.ListPrice, 50, '0', DEFAULT)
     WHEN @SortColumn4 = 5 THEN
     dbo.[udfGetIntegerAsPaddedString](P.DaysToManufacture, 50, '0')
     ELSE NULL
   END),
  (CASE WHEN @SortColumn5 = 1 THEN CONVERT(VARCHAR(50), P.Name)
     WHEN @SortColumn5 = 2 THEN CONVERT(VARCHAR(50), P.ProductNumber)
     WHEN @SortColumn5 = 3 THEN CONVERT(VARCHAR(50), P.Color)
     WHEN @SortColumn5 = 4 THEN 
     dbo.[udfGetMoneyAsPaddedString](P.ListPrice, 50, '0', DEFAULT)
     WHEN @SortColumn5 = 5 THEN
     dbo.[udfGetIntegerAsPaddedString](P.DaysToManufacture, 50, '0')
     ELSE NULL
   END)

END --Procedure

--**********************************************************************************************
--TESTING / EXAMPLE EXECTUTIONS
--**********************************************************************************************

--Run the routine with NO SORTING specified
EXEC uspProducts_ReturnProductsInDesiredSortOrder NULL, NULL, NULL, NULL, NULL

--Sort by just ProductNumber (notice - it doesn't matter WHICH PARM contains the NON-NULL value)
EXEC uspProducts_ReturnProductsInDesiredSortOrder 2, NULL, NULL, NULL, NULL
EXEC uspProducts_ReturnProductsInDesiredSortOrder NULL, NULL, NULL, 2, NULL

--Sort by DaysToManufacture, then ListPrice, then Name
EXEC uspProducts_ReturnProductsInDesiredSortOrder 5, NULL, 4, 1, NULL

--Sort by ListPrice, Color, ProductNumber then Name
EXEC uspProducts_ReturnProductsInDesiredSortOrder 4, 3, 2, 1, NULL

--**********************************************************************************************
-- END: NON-DYNAMIC-SQL with DYNAMIC ORDER-BY specified at run-time via Parameters #1.
--**********************************************************************************************

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.