t-sql存储过程_使用T-SQL进行存储过程中ORDER BY的精确控制

于 2020-07-21 04:19:56 发布
阅读量822 收藏
点赞数
文章标签： 数据库 java python mysql sql
原文链接：https://www.sqlshack.com/lever-t-sql-for-pinpoint-control-of-order-by-in-a-stored-procedure/
版权

t-sql存储过程

介绍 (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.

T-SQL ORDER BY子句对SQL Server SELECT语句结果集进行排序，当我们构建存储过程时，它变得很重要。 不幸的是，该语法没有灵活的方法来直接控制带有参数值的ORDER BY子句行为。 这意味着我们没有一种简单的方法来控制ORDER BY子句排序的特定列。 此外，SQL Server没有提供一种灵活的方法来直接控制带有参数值的任何ORDER BY子句列的升序或降序。 当然，我们当然可以在存储过程中对ORDER BY子句进行硬编码，但是这种方法一成不变。 我们可以尝试动态SQL解决方案，其中涉及 可以在存储过程内部动态生成和执行SQL Server语句的存储过程代码。 但是，此技术变得棘手，并且可能导致SQL注入攻击。 其他技术可能依赖于CASE语句，并且随着列数的增加，它们的复杂性会变得不堪重负。 本文着重介绍一种干净，高效，精确的T-SQL存储过程技术，该技术可直接对一个，某些或所有SELECT语句结果集列进行排序。 该技术避免了动态SQL，并且直接在存储过程中进行操作。 本文还介绍了如何设置特定列的升序或降序排序。

样本数据库 (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:

对于本文，我们将从OFFICE_EQUIPMENT_DATABASE数据库开始，该数据库首先在较早SQL Shack文章中进行了介绍 。 较早的文章在顶部附近有一个创建脚本，它将创建OFFICE_EQUIPMENT_DATABASE。 将脚本复制到“ SQL Server Management Studio”选项卡中，然后运行它。 我在更新的Windows 10 PC上SQL Server 2014 Standard Edition中构建了该数据库。 对于本文，我们将向OFFICE_EQUIPMENT数据库表添加八行，并将向数据库本身添加存储过程。 运行以下代码以将此新材料添加到数据库中： 

USE [OFFICE_EQUIPMENT_DATABASE]
 
INSERT [dbo].[OFFICE_EQUIPMENT] ([OFFICE_EQUIPMENT_ID], [OFFICE_EQUIPMENT_NAME], [OFFICE_EQUIPMENT_DESCRIPTION], [PURCHASE_PRICE], [PURCHASE_QUANTITY], [PURCHASE_DATE]) VALUES (17, N'PRINTER PAPER', N'20 LB. PRINTER PAPER (ONE REAM)', 3.4900, 22, CAST(N'2020-01-18 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[OFFICE_EQUIPMENT] ([OFFICE_EQUIPMENT_ID], [OFFICE_EQUIPMENT_NAME], [OFFICE_EQUIPMENT_DESCRIPTION], [PURCHASE_PRICE], [PURCHASE_QUANTITY], [PURCHASE_DATE]) VALUES (18, N'PAPER CLIP', N'SMALL AND MEDIUM PAPER CLIPS', 3.1900, 8, CAST(N'2019-01-28 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[OFFICE_EQUIPMENT] ([OFFICE_EQUIPMENT_ID], [OFFICE_EQUIPMENT_NAME], [OFFICE_EQUIPMENT_DESCRIPTION], [PURCHASE_PRICE], [PURCHASE_QUANTITY], [PURCHASE_DATE]) VALUES (19, N'ENVELOPE', N'PRE-ADDRESSED AND PRE-STAMPED', 0.0500, 750, CAST(N'2019-08-22 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[OFFICE_EQUIPMENT] ([OFFICE_EQUIPMENT_ID], [OFFICE_EQUIPMENT_NAME], [OFFICE_EQUIPMENT_DESCRIPTION], [PURCHASE_PRICE], [PURCHASE_QUANTITY], [PURCHASE_DATE]) VALUES (20, N'PAPER CLIPS', N'MEDIUM AND JUMBO PAPER CLIPS', 3.7900, 8, CAST(N'2019-11-12 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[OFFICE_EQUIPMENT] ([OFFICE_EQUIPMENT_ID], [OFFICE_EQUIPMENT_NAME], [OFFICE_EQUIPMENT_DESCRIPTION], [PURCHASE_PRICE], [PURCHASE_QUANTITY], [PURCHASE_DATE]) VALUES (21, N'PEN', N'PENTEL BALLPOINT PEN (RED)', 0.7000, 42, CAST(N'2019-01-08 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[OFFICE_EQUIPMENT] ([OFFICE_EQUIPMENT_ID], [OFFICE_EQUIPMENT_NAME], [OFFICE_EQUIPMENT_DESCRIPTION], [PURCHASE_PRICE], [PURCHASE_QUANTITY], [PURCHASE_DATE]) VALUES (22, N'PENCIL', N'#2 PENCIL', 0.0800, 150, CAST(N'2020-02-17 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[OFFICE_EQUIPMENT] ([OFFICE_EQUIPMENT_ID], [OFFICE_EQUIPMENT_NAME], [OFFICE_EQUIPMENT_DESCRIPTION], [PURCHASE_PRICE], [PURCHASE_QUANTITY], [PURCHASE_DATE]) VALUES (23, N'GRAY STAPLER', N'SWINGLINE STAPLER - 20 SHEET CAPACITY', 5.1100, 3, CAST(N'2018-10-01 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[OFFICE_EQUIPMENT] ([OFFICE_EQUIPMENT_ID], [OFFICE_EQUIPMENT_NAME], [OFFICE_EQUIPMENT_DESCRIPTION], [PURCHASE_PRICE], [PURCHASE_QUANTITY], [PURCHASE_DATE]) VALUES (24, N'GRAY STAPLER', N'SWINGLINE STAPLER - 20 SHEET CAPACITY', 5.1100, 3, CAST(N'2018-10-01 00:00:00.000' AS DateTime))
GO
    
CREATE PROCEDURE [dbo].[TSQL_SORTING]
    
            @SORT_COLUMN_LIST bigint
    
AS
    
/*
            To use:
    
                EXEC TSQL_SORTING 000001
                EXEC TSQL_SORTING 000002
                EXEC TSQL_SORTING 001001
                EXEC TSQL_SORTING 002002
                EXEC TSQL_SORTING 001011
                EXEC TSQL_SORTING 002012
                EXEC TSQL_SORTING 010001
                EXEC TSQL_SORTING 020002
*/
    
SELECT		OFFICE_EQUIPMENT_ID, OFFICE_EQUIPMENT_NAME, OFFICE_EQUIPMENT_DESCRIPTION,
                PURCHASE_PRICE, PURCHASE_QUANTITY, PURCHASE_DATE
FROM		OFFICE_EQUIPMENT
ORDER BY	IIF(RIGHT((@SORT_COLUMN_LIST / CAST(100000 AS BIGINT)), 1) = 2, OFFICE_EQUIPMENT_ID, NULL) ASC,
                IIF(RIGHT((@SORT_COLUMN_LIST / CAST(100000 AS BIGINT)), 1) = 1, OFFICE_EQUIPMENT_ID, NULL) DESC,
                IIF(RIGHT((@SORT_COLUMN_LIST / CAST(10000 AS BIGINT)), 1) = 2, OFFICE_EQUIPMENT_NAME, NULL) ASC,
                IIF(RIGHT((@SORT_COLUMN_LIST / CAST(10000 AS BIGINT)), 1) = 1, OFFICE_EQUIPMENT_NAME, NULL) DESC,
                IIF(RIGHT((@SORT_COLUMN_LIST / CAST(1000 AS BIGINT)), 1) = 2, OFFICE_EQUIPMENT_DESCRIPTION, NULL) ASC,
                IIF(RIGHT((@SORT_COLUMN_LIST / CAST(1000 AS BIGINT)), 1) = 1, OFFICE_EQUIPMENT_DESCRIPTION, NULL) DESC,
                IIF(RIGHT((@SORT_COLUMN_LIST / CAST(100 AS BIGINT)), 1) = 2, PURCHASE_PRICE, NULL) ASC,
                IIF(RIGHT((@SORT_COLUMN_LIST / CAST(100 AS BIGINT)), 1) = 1, PURCHASE_PRICE, NULL) DESC,
                IIF(RIGHT((@SORT_COLUMN_LIST / CAST(10 AS BIGINT)), 1) = 2, PURCHASE_QUANTITY, NULL) ASC,
                IIF(RIGHT((@SORT_COLUMN_LIST / CAST(10 AS BIGINT)), 1) = 1, PURCHASE_QUANTITY, NULL) DESC,
                IIF(RIGHT(@SORT_COLUMN_LIST, 1) = 2, PURCHASE_DATE, NULL) ASC,
                IIF(RIGHT(@SORT_COLUMN_LIST, 1) = 1, PURCHASE_DATE, NULL) DESC

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.

有关数据库，表和表结构的更多详细信息，请参见前面的文章。 本文将重点介绍TSQL_SORTING存储过程中的T-SQL工程。

运行中的存储过程 (The Stored Procedure In Action)

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

OFFICE_EQUIPMENT_DATABASE数据库具有一个存储过程TSQL_SORTING ，其代码如下： 

CREATE PROCEDURE [dbo].[TSQL_SORTING]
 
    @SORT_COLUMN_LIST bigint
 
AS
 
/*
    To use:
 
        EXEC TSQL_SORTING 000001
        EXEC TSQL_SORTING 000002
        EXEC TSQL_SORTING 001001
        EXEC TSQL_SORTING 002002
        EXEC TSQL_SORTING 001011
        EXEC TSQL_SORTING 002012
        EXEC TSQL_SORTING 010001
        EXEC TSQL_SORTING 020002
*/
 
SELECT		OFFICE_EQUIPMENT_ID, OFFICE_EQUIPMENT_NAME, OFFICE_EQUIPMENT_DESCRIPTION,
        PURCHASE_PRICE, PURCHASE_QUANTITY, PURCHASE_DATE
FROM		OFFICE_EQUIPMENT
ORDER BY	IIF(RIGHT((@SORT_COLUMN_LIST / CAST(100000 AS BIGINT)), 1) = 2, OFFICE_EQUIPMENT_ID, NULL) ASC,
        IIF(RIGHT((@SORT_COLUMN_LIST / CAST(100000 AS BIGINT)), 1) = 1, OFFICE_EQUIPMENT_ID, NULL) DESC,
        IIF(RIGHT((@SORT_COLUMN_LIST / CAST(10000 AS BIGINT)), 1) = 2, OFFICE_EQUIPMENT_NAME, NULL) ASC,
        IIF(RIGHT((@SORT_COLUMN_LIST / CAST(10000 AS BIGINT)), 1) = 1, OFFICE_EQUIPMENT_NAME, NULL) DESC,
        IIF(RIGHT((@SORT_COLUMN_LIST / CAST(1000 AS BIGINT)), 1) = 2, OFFICE_EQUIPMENT_DESCRIPTION, NULL) ASC,
        IIF(RIGHT((@SORT_COLUMN_LIST / CAST(1000 AS BIGINT)), 1) = 1, OFFICE_EQUIPMENT_DESCRIPTION, NULL) DESC,
        IIF(RIGHT((@SORT_COLUMN_LIST / CAST(100 AS BIGINT)), 1) = 2, PURCHASE_PRICE, NULL) ASC,
        IIF(RIGHT((@SORT_COLUMN_LIST / CAST(100 AS BIGINT)), 1) = 1, PURCHASE_PRICE, NULL) DESC,
        IIF(RIGHT((@SORT_COLUMN_LIST / CAST(10 AS BIGINT)), 1) = 2, PURCHASE_QUANTITY, NULL) ASC,
        IIF(RIGHT((@SORT_COLUMN_LIST / CAST(10 AS BIGINT)), 1) = 1, PURCHASE_QUANTITY, NULL) DESC,
        IIF(RIGHT(@SORT_COLUMN_LIST, 1) = 2, PURCHASE_DATE, NULL) ASC,
    IIF(RIGHT(@SORT_COLUMN_LIST, 1) = 1, PURCHASE_DATE, NULL) DESC

as seen in this screenshot:

如该屏幕截图所示：

The TSQL_SORTING stored procedure, with code in the ORDER BY clause that makes flexible sorting possible.

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:

此存储过程在第4行有一个bigint或大整数T-SQL数据类型参数@SORT_COLUMN_LIST 。我们可以手动“绘制”并运行11至18行进行测试。 此屏幕快照运行第15行EXEC TSQL_SORTING 001011 ，参数值为001011 ：

Testing the TSQL_SORTING stored procedure with an EXEC statement.

Here, the result set sorted the rows by

在这里，结果集对行进行了排序

  • OFFICE_EQUIPMENT_DESCRIPTION (column 3 descending)

    OFFICE_EQUIPMENT_DESCRIPTION（第3列降序）
  • PURCHASE_QUANTITY (column 5 descending)

    PURCHASE_QUANTITY（第5列降序）
  • PURCHASE_DATE (column 6 descending)

    PURCHASE_DATE（第6列降序）

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

基于001011参数值。 此屏幕快照运行第16行， EXEC TSQL_SORTING 002012 ，参数值为002012 ：

Testing the TSQL_SORTING stored procedure with an EXEC statement.

Here, the result set sorted the rows by

在这里，结果集对行进行了排序

  • OFFICE_EQUIPMENT_DESCRIPTION (column 3 ascending)

    OFFICE_EQUIPMENT_DESCRIPTION（第3列升序）
  • PURCHASE_QUANTITY (column 5 descending)

    PURCHASE_QUANTITY（第5列降序）
  • PURCHASE_DATE (column 6 ascending)

    PURCHASE_DATE（第6列升序）

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

基于002012参数值。 接下来，我们将了解T-SQL工程的工作原理。

存储过程工程 (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:

在本文中，第一个屏幕截图显示了TSQL_SORTING存储过程，在研究其工程设计时，我们将再次显示该屏幕截图：

The TSQL_SORTING stored procedure, with code in the ORDER BY clause that makes flexible sorting possible.

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:

在第4行声明的@SORT_COLUMN_LIST参数具有大整数或bigint数据类型。 微软解释说 ，bigint数据可以有19位数字。 我们将看到， TSQL_SORTING存储过程将@SORT_COLUMN_LIST中的每个数字映射到一个单独的结果集列。 这意味着TSQL_SORTING工程可以在一个结果集中覆盖多达19列。 本文中显示的示例涉及具有六列的结果集，因此示例中的@SORT_COLUMN_LIST值具有六位数字。 TSQL_SORTING工程还期望每个@SORT_COLUMN_LIST数字的值只能在0、1或2范围内。 第24和35行之间的T-SQL工程解析每个参数数字，并将每个解析的参数数字映射到特定的结果集列。 例如，此图显示参数值002012如何映射到结果集列：

Mapping @SORT_COLUMN_LIST component digits to TSQL_SORTING 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:

在对TSQL_SORTING， EXEC TSQL_SORTING 002012的此调用中， @ SORT_COLUMN_LIST参数的值为002012 。 根据上图，此值的数字3、5和6具有以下映射：

  • 3 (value 2) <-> OFFICE_EQUIPMENT_DESCRIPTION

    3（值2）<-> OFFICE_EQUIPMENT_DESCRIPTION
  • 5 (value 1) <-> PURCHASE_QUANTITY

    5（值1）<-> PURCHASE_QUANTITY
  • 6 (value 2) <-> PURCHASE_DATE

    6（值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:

TSQL_SORTING T-SQL工程使用这些值来构造对结果集进行排序的方式。 数字1、2和4具有以下映射：

  • 1 (value 0) <-> OFFICE_EQUIPMENT_ID

    1（值0）<-> OFFICE_EQUIPMENT_ID
  • 2 (value 0) <-> OFFICE_EQUIPMENT_NAME

    2（值0）<-> OFFICE_EQUIPMENT_NAME
  • 4 (value 0) <-> PURCHASE_PRICE

    4（值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.

TSQL_SORTING忽略了这些列，因为参数数字1、2和4的值为零。 接下来，我们将了解所有这些工作原理。

Lines 28

第28行 

IIF(RIGHT((@SORT_COLUMN_LIST / CAST(1000 AS BIGINT)), 1) = 2, OFFICE_EQUIPMENT_DESCRIPTION, NULL) ASC

and 29

和29 

IIF(RIGHT((@SORT_COLUMN_LIST / CAST(1000 AS BIGINT)), 1) = 1, OFFICE_EQUIPMENT_DESCRIPTION, NULL) DESC

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.”

处理OFFICE_EQUIPMENT_DESCRIPTION列（ SELECT子句中的第三列）的可能排序。 这些行的操作几乎相同，我们将从内到外解压缩它们，并了解为什么它们作为“组”运行。

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:

第28和29 行在第三列– OFFICE_EQUIPMENT_DESCRIPTION上运行 。 关注第28行，左起@SORT_COLUMN_LIST的第三位映射到第三列，因此我们需要构建T-SQL代码以从参数中提取该位。 @SORT_COLUMN_LIST / CAST（1000 AS BIGINT）首先计算CAST，或将1000转换为bigint数据类型。 @SORT_COLUMN_LIST参数具有bigint数据类型，对于此参数的“较大”值，我们需要bigint数据类型的除数以确保除法运算将正确计算。 在此存储过程中，对所有除法操作都使用CAST功能是一种好习惯。 计算将@SORT_COLUMN_LIST除以1000，然后丢弃或截断其余部分。 如果以这种方式将六位数的整数除以1000，我们将删除右边的三位数，如以下屏幕截图所示：

Example queries: the / (DIVISION) operator.

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:

继续使用002012作为原始@SORT_COLUMN_LIST值， 到目前为止 ，我们已将该值解析为002 。 现在，我们需要删除002的前两位数字。 RIGHT（）函数RIGHT（（@ SORT_COLUMN_LIST / CAST（1000 AS BIGINT）），1）处理此任务。 它提取002的最右边的数字-在这种情况下为两（2）。 在上一个屏幕快照中，查询二和查询三表明我们需要T-SQL RIGHT（）函数来正式仅获取最右边的特定数字。 有了该数字后，“外部”的IIF（）函数将查看该数字的值，如下所示： 

IIF(RIGHT((@SORT_COLUMN_LIST / CAST(1000 AS BIGINT)), 1) = 2, OFFICE_EQUIPMENT_DESCRIPTION, NULL)

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:

我们可以在此处使用SQL Server CASE表达式 ，但是，我们将IIF（）函数用作简写。 如前所述，所有@SORT_COLUMN_LIST数字将仅具有0、1或2的值。如果RIGHT（）函数返回2，则第28行IIF（）函数内部的布尔表达式返回TRUE ，而该特定IIF（ ）函数返回OFFICE_EQUIPMENT_DESCRIPTION。 这会将OFFICE_EQUIPMENT_NAME列放在ORDER BY子句中。 如果RIGHT（）函数返回任何其他值，则第28行IIF（）函数内部的布尔表达式返回FALSE ，并且该特定IIF（）函数返回NULL 。 此图显示了@SORT_COLUMN_LIST = 002012时第28行的工作方式：

Unpack the line 28 IIF function 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.

由于存储过程构建ORDER BY子句，它忽略每一个NULL值，在开始按每IIF（）函数BY子句可能返回。 结果，当第24行和第35行之间的ORDER BY子句中的任何IIF（）函数返回NULL值时，该存储过程将忽略该IIF（）函数的关联列名称“ 拥有 ”。

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

第28行以ASC 结尾 ，为OFFICE_EQUIPMENT_COLUMN设置升序排序，如下所示： 

IIF(RIGHT((@SORT_COLUMN_LIST / CAST(1000 AS BIGINT)), 1) = 2, OFFICE_EQUIPMENT_DESCRIPTION, NULL) ASC

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.

如果第28 行将OFFICE_EQUIPMENT_DESCRIPTION放在ORDER BY子句中，则存储过程T-SQL代码将自动包含ASC 。 否则，存储过程将忽略ASC关键字。

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

第29行与第28行几乎相同，如下所示： 

IIF(RIGHT((@SORT_COLUMN_LIST / CAST(1000 AS BIGINT)), 1) = 1, OFFICE_EQUIPMENT_DESCRIPTION, NULL) DESC

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:

此IIF（）布尔表达式测试值为1 。 与第28行一样，如果此测试返回TRUE ，则TSQL_SORTING将OFFICE_EQUIPMENT_NAME放在ORDER BY子句中，但是第29行自动包含DESC以对该列进行降序排序。 此图显示了@SORT_COLUMN_LIST = 002012时第29行的工作方式：

Unpack the line 29 IIF function when @SORT_COLUMN_LIST = 002012.

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

总而言之，第28行和第29行是一个组，如下所示： 

IIF(RIGHT((@SORT_COLUMN_LIST / CAST(1000 AS BIGINT)), 1) = 2, OFFICE_EQUIPMENT_DESCRIPTION, NULL) ASC,
IIF(RIGHT((@SORT_COLUMN_LIST / CAST(1000 AS BIGINT)), 1) = 1, OFFICE_EQUIPMENT_DESCRIPTION, NULL) DESC,

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.

如果@SORT_COLUMN_LIST的第三位数字值为2，则第28 行将OFFICE_EQUIPMENT_DESCRIPTION放在ORDER BY子句中，并包括ASC关键字。 第29行的第三位数字值为2，但不会在ORDER BY子句中放置任何内容，因为其布尔表达式返回FALSE 。 此外， TSQL_SORTING存储过程将忽略第29行的T-SQL DESC关键字。 如果@SORT_COLUMN_LIST在数字3处的值为1， 则会发生类似的行为。在这种情况下，第29 行将OFFICE_EQUIPMENT_DESCRIPTION放在ORDER BY子句中，并包含DESC关键字。 如果第三个@SORT_COLUMN_LIST数字的值为0，则第28行和第29行均返回NULL 。 在这种情况下， TSQL_SORTING存储过程在构建ORDER BY子句时将完全忽略OFFICE_EQUIPMENT_DESCRIPTION 。 在这些行的末尾还将忽略T-SQL ASC和DESC关键字。

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:

第24到33行如上所述成对运行，以覆盖前五个@SORT_COLUMN_LIST数字。 这些ORDER BY块行对将对数的对数更改为10的因数，以成对地对@SORT_COLUMN_LIST参数值中的数字进行逐位解析。 在特殊情况下，第34和35行会避免除法计算，如下所示： 

IIF(RIGHT(@SORT_COLUMN_LIST, 1) = 2, PURCHASE_DATE, NULL) ASC
IIF(RIGHT(@SORT_COLUMN_LIST, 1) = 1, PURCHASE_DATE, NULL) DESC

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

之所以可行，是因为RIGHT（）函数可以直接提取最右边的@SORT_COLUMN_LIST数字。

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

在ORDER BY子句中，此技术将两行成对分组，以处理结果集中的每一列。 三个可能的数值

  • 2 (ASCending)

    2（升序）
  • 1 (DESCending)

    1（降序）
  • 0 (not included)

    0（不包括在内）

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

对于每个@SORT_COLUMN_LIST参数数字，请覆盖ORDER BY子句中每一列的所有可能情况。

结论 (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.

本文表明，T-SQL IIF（）和RIGHT（）函数与除法运算符和ORDER BY子句结合使用，提供了一种干净，灵活，精确的方法来对SQL Server SELECT结果集进行排序。 使用这种技术，我们可以避免动态SQL，复杂的CASE语句以及其他许多令人头疼的麻烦。

翻译自: https://www.sqlshack.com/lever-t-sql-for-pinpoint-control-of-order-by-in-a-stored-procedure/

t-sql存储过程

culuo4781
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值