Introduction

The T-SQL ORDER BY clause sorts SQL Server SELECT statement result sets, and it becomes important when we build stored procedures. Unfortunately, the syntax offers no flexible way to directly control the ORDER BY clause behavior with argument values. This means we don’t have an easy way to control the specific column or columns that the ORDER BY clause sorts. Additionally, SQL Server does not offer a flexible way to directly control the ascending or descending order of any ORDER BY clause column with argument values. Of course, we can certainly hard-code the ORDER BY clause in a stored procedure, but this approach becomes fixed in stone. We could try a dynamic SQL solution, involving a stored procedure code that dynamically builds and executes SQL Server statements inside a stored procedure. However, this technique becomes tricky, and it can lead to SQL injection attacks. Other techniques might rely on CASE statements, and their complexity can become overwhelming as the column count grows. This article spotlights a clean, efficient, pinpoint T-SQL stored procedure technique that directly sorts one, some, or all SELECT statement result set columns. The technique avoids dynamic SQL, and it operates directly in a stored procedure. The article also shows how to set the ascending or descending sort order of specific columns.

The Sample Database

For this article, we’ll start with the OFFICE_EQUIPMENT_DATABASE database, first described in an earlier SQL Shack article. That earlier article has a creation script near the top that will create the OFFICE_EQUIPMENT_DATABASE. Copy the script into a SQL Server Management Studio tab, and then run it. I built this database in SQL Server 2014 Standard Edition, on an updated Windows 10 PC. For this article, we’ll add eight rows to the OFFICE_EQUIPMENT database table, and we’ll add a stored procedure to the database itself. Run the following code to add this new material to the database:

See the earlier article for more details about the database, the table, and the table structure. This article will focus on T-SQL engineering in the TSQL_SORTING stored procedure.

The Stored Procedure In Action

The OFFICE_EQUIPMENT_DATABASE database has one stored procedure TSQL_SORTING with this code:

as seen in this screenshot:

This stored procedure has one bigint, or big integer, T-SQL data type parameter @SORT_COLUMN_LIST at line 4. We can manually “paint” and run lines 11 through 18 for testing. This screenshot runs line 15 EXEC TSQL_SORTING 001011 with the 001011 argument value:

Here, the result set sorted the rows by

• OFFICE_EQUIPMENT_DESCRIPTION (column 3 descending)
• PURCHASE_QUANTITY (column 5 descending)
• PURCHASE_DATE (column 6 descending)

based on the 001011 argument value. This screenshot runs line 16, EXEC TSQL_SORTING 002012 with a 002012 argument value:

Here, the result set sorted the rows by

• OFFICE_EQUIPMENT_DESCRIPTION (column 3 ascending)
• PURCHASE_QUANTITY (column 5 descending)
• PURCHASE_DATE (column 6 ascending)

based on the 002012 argument value. Next, we’ll see how T-SQL engineering works.

The Stored Procedure Engineering

In this article, the first screenshot showed the TSQL_SORTING stored procedure, and we’ll show the screenshot again as we study its engineering:

The @SORT_COLUMN_LIST parameter declared at line 4 has a big integer, or bigint, data type. Microsoft explains that bigint data can have 19 digits. As we’ll see, the TSQL_SORTING stored procedure maps each digit in @SORT_COLUMN_LIST to one individual result set column. This means that the TSQL_SORTING engineering can cover as many as 19 columns in a result set. The examples shown in this article involve result sets with six columns, so the @SORT_COLUMN_LIST values in the examples have six digits. The TSQL_SORTING engineering also expects that each @SORT_COLUMN_LIST digit will have a value only in the 0, 1, or 2 range. The T-SQL engineering between lines 24 and 35 parses each parameter digit and maps each parsed parameter digit to a specific result set column. For example, this diagram shows how a parameter value of 002012 maps to the result set columns:

In this call to TSQL_SORTING, EXEC TSQL_SORTING 002012 the @SORT_COLUMN_LIST argument has a value of 002012. Based on the above diagram, digits 3, 5, and 6 of this value have these mappings:

• 3 (value 2) <-> OFFICE_EQUIPMENT_DESCRIPTION
• 5 (value 1) <-> PURCHASE_QUANTITY
• 6 (value 2) <-> PURCHASE_DATE

The TSQL_SORTING T-SQL engineering uses these values to structure the way it sorts its result set. Digits 1, 2, and 4 have these mappings:

• 1 (value 0) <-> OFFICE_EQUIPMENT_ID
• 2 (value 0) <-> OFFICE_EQUIPMENT_NAME
• 4 (value 0) <-> PURCHASE_PRICE

TSQL_SORTING ignored these columns because parameter digits 1, 2, and 4 have values of zero. Next, we’ll see how all this works.

Lines 28

and 29

handle potential sorting for the OFFICE_EQUIPMENT_DESCRIPTION column ‑ the third column in the SELECT clause. These lines operate almost the same, we’ll unpack them from the inside out, and we’ll see why they operate as a “group.”

Lines 28 and 29 operate on the third column – OFFICE_EQUIPMENT_DESCRIPTION. Focusing on line 28, the third digit of @SORT_COLUMN_LIST from the left maps to column three, so we need to build the T-SQL code that extracts this digit from the parameter. The @SORT_COLUMN_LIST / CAST(1000 AS BIGINT)calculation first CASTs, or converts, 1000 to a bigint data type. The @SORT_COLUMN_LIST parameter has a bigint data type, and for “larger” values of this parameter, we need a divisor of the bigint data type to guarantee that the division operation will calculate correctly. It becomes a good practice to use the CAST function for all division operations in this stored procedure. The calculation divides @SORT_COLUMN_LIST by 1000, and throws away, or truncates, the remainder. If we divide a six-digit integer by 1000 in this way, we will remove the three digits on the right, as shown in this screenshot:

Continuing with 002012 as the original @SORT_COLUMN_LIST value, we parsed down this value to 002 so far. Now, we need to remove the first two digits of 002. The RIGHT() function RIGHT((@SORT_COLUMN_LIST / CAST(1000 AS BIGINT)), 1) handles this task. It extracts the right-most digit of 002 – in this case, a two (2). In the previous screenshot, queries two and three show that we need the T-SQL RIGHT() function, to formally grab only the specific rightmost digit. Once we have that digit, the IIF() function on the “outside” looks at the value of that digit, as seen here:

We could use the SQL Server CASE expression here, but instead, we’ll use the IIF() function as a short-hand. As explained earlier, all of the @SORT_COLUMN_LIST digits will only have values of 0, 1, or 2. If the RIGHT() function returns 2, the Boolean expression inside the line 28 IIF() function returns TRUE, and that specific IIF() function returns OFFICE_EQUIPMENT_DESCRIPTION. This places the OFFICE_EQUIPMENT_NAME column in the ORDER BY clause. If the RIGHT() function returns any other value, the Boolean expression inside the line 28 IIF() function returns FALSE, and that specific IIF() function returns NULL. This image shows how line 28 works when @SORT_COLUMN_LIST = 002012:

As the stored procedure builds the ORDER BY clause, it ignores every NULL value that every IIF() function in the ORDER BY clause might return. As a result, when any IIF() function in the ORDER BY clause, between lines 24 and 35, returns a NULL value, the stored procedure ignores the associated column name “owned” by that IIF() function.

Line 28 ends with ASC to set an ascending sort order for the OFFICE_EQUIPMENT_COLUMN, as shown here:

If line 28 places OFFICE_EQUIPMENT_DESCRIPTION in the ORDER BY clause, the stored procedure T-SQL code will automatically include the ASC. Otherwise, the stored procedure will ignore the ASC keyword.

Line 29 operates almost the same as line 28, as shown here:

This IIF() Boolean expression tests for a value of 1. As inline 28, if this test returns TRUE, TSQL_SORTING places OFFICE_EQUIPMENT_NAME in the ORDER BY clause, but line 29 automatically includes DESC to sort this column in descending order. This image shows how line 29 works when @SORT_COLUMN_LIST = 002012:

Taken together, lines 28 and 29 operate as a group, as seen here:

If @SORT_COLUMN_LIST has a third digit value of 2, line 28 places OFFICE_EQUIPMENT_DESCRIPTION in the ORDER BY clause, and includes the ASC keyword. Line 29 will see the third digit value of 2 but will place nothing in the ORDER BY clause because its Boolean expression returns FALSE. Additionally, the TSQL_SORTING stored procedure will ignore the line 29 T-SQL DESC keyword. Similar behavior happens if @SORT_COLUMN_LIST has a value of 1 at digit 3. In this case, line 29 places OFFICE_EQUIPMENT_DESCRIPTION in the ORDER BY clause and includes the DESC keyword. If the third @SORT_COLUMN_LIST digit has a value of 0, lines 28 and 29 both return NULL. In this case, the TSQL_SORTING stored procedure will completely ignore OFFICE_EQUIPMENT_DESCRIPTION as it builds the ORDER BY clause. It will also ignore the T-SQL ASC and DESC keywords at the ends of those lines.

Lines 24 to 33 operate in pair groups as described above, to cover the first five @SORT_COLUMN_LIST digits. These ORDER BY block line pairs change the divisor value by a factor of 10, from pair to pair, to parse from digit to digit in the @SORT_COLUMN_LIST parameter value. As special cases, lines 34 and 35 avoid the division calculation, as shown here:

This works because the RIGHT() function can directly extract the rightmost @SORT_COLUMN_LIST digit.

In the ORDER BY clause, this technique groups two lines, as a pair, to handle each column in the result set. Three possible number values

• 2 (ASCending)
• 1 (DESCending)
• 0 (not included)

for each @SORT_COLUMN_LIST parameter digit, cover all possible cases, for each column, in the ORDER BY clause.

Conclusion

This article shows that the T-SQL IIF() and RIGHT() functions, combined with the division operator and the ORDER BY clause, offer a clean, flexible, pinpoint a way to sort a SQL Server SELECT result set. With this technique, we can avoid the headaches and hassles of dynamic SQL, complex CASE statements, and much else.

• Author
• Recent Posts

Frank Solomon

Frank Solomon started out building Microsoft stack products, and he gradually focused on SQL Server. Some years ago, he began a parallel shift to writing and technical writing. He wrote published articles, he blogs at Bit Vectors, and he co-wrote The SQL Workshop for Packt Publishing, with SQL Shack writer Prashanth Jayaram.

See more about Frank at LinkedIn.
Remove the non-standard characters from this address: fb} <s.author@gmail.com to reach him by email.

Latest posts by Frank Solomon (see all)

• Lever the TSQL MAX/MIN/IIF functions for Pinpoint Row Pivots - May 16, 2022
• Use Kusto Query Language to solve a data problem - July 5, 2021
• Azure Data Explorer and the Kusto Query Language - June 21, 2021