在公共新闻组中,一个经常出现的问题是“怎样才能根据传递给存储过程的参数返回一个排序的输出?”。在一些高水平专家的帮助之下,我整理出了这个问题的几种解决方案。
一、用IF...ELSE执行预先编写好的查询
对于大多数人来说,首先想到的做法也许是:通过IF...ELSE语句,执行几个预先编写好的查询中的一个。例如,假设要从Northwind数据库查询得到一个货主(Shipper)的排序列表,发出调用的代码以存储过程参数的形式指定一个列,存储过程根据这个列排序输出结果。Listing 1显示了这种存储过程的一个可能的实现(GetSortedShippers存储过程)。
【Listing 1: 用IF...ELSE执行多个预先编写好的查询中的一个】
CREATE PROC GetSortedShippers
@OrdSeq AS int
AS
IF @OrdSeq = 1
SELECT * FROM Shippers ORDER BY ShipperID
ELSE IF @OrdSeq = 2
SELECT * FROM Shippers ORDER BY CompanyName
ELSE IF @OrdSeq = 3
SELECT * FROM Shippers ORDER BY Phone
这种方法的优点是代码很简单、很容易理解,SQL Server的查询优化器能够为每一个SELECT查询创建一个查询优化计划,确保代码具有最优的性能。这种方法最主要的缺点是,如果查询的要求发生了改变,你必须修改多个独立的SELECT查询——在这里是三个。
二、用列名字作为参数
另外一个选择是让查询以参数的形式接收一个列名字。Listing 2显示了修改后的GetSortedShippers存储过程。CASE表达式根据接收到的参数,确定SQL Server在ORDER BY子句中使用哪一个列值。注意,ORDER BY子句中的表达式并未在SELECT清单中出现。在ANSI SQL-92标准中,ORDER BY子句中不允许出现没有在SELECT清单中指定的表达式,但ANSI SQL-99标准允许。SQL Server一直允许这种用法。
【Listing 2:用列名字作为参数,第一次尝试】
CREATE PROC GetSortedShippers
@ColName AS sysname
AS
SELECT *
FROM Shippers
ORDER BY
CASE @ColName
WHEN 'ShipperID' THEN ShipperID
WHEN 'CompanyName' THEN CompanyName
WHEN 'Phone' THEN Phone
ELSE NULL
END
现在,我们来试一下新的存储过程,以参数的形式指定ShipperID列:
EXEC GetSortedShippers 'ShipperID'
此时一切正常。但是,当我们视图把CompanyName列作为参数调用存储过程时,它不再有效:
EXEC GetSortedShippers 'CompanyName'
仔细看一下错误信息:
Server: Msg 245, Level 16, State 1, Procedure GetSortedShippers, Line 5
Syntax error converting the nvarchar value 'Speedy
Express' to a column of data type int.
它显示出,SQL Server试图把“Speedy Express”(nvarchar数据类型)转换成一个整数值——当然,这个操作是不可能成功的。出现错误的原因在于,按照“数据类型优先级”规则,CASE表示式中最高优先级的数据类型决定了表达式返回值的数据类型。“数据类型优先级”规则可以在SQL Server Books Online(BOL)找到,它规定了int数据类型的优先级要比nvarchar数据类型高。前面的代码要求SQL Server按照CompanyName排序输出,CompanyName是nvarchar数据类型。这个CASE表达式的返回值可能是ShipperID(int类型),可能是CompanyName(nvarchar类型),或Phone(nvarchar类型)。由于int类型具有较高的优先级,因此CASE表达式返回值的数据类型应该是int。
为了避免出现这种转换错误,我们可以尝试把ShipperID转换成varchar数据类型。采用这种方法之后,nvarchar将作为最高优先级的数据类型被返回。Listing 3显示了修改后的GetSortedShippers存储过程。
【Listing 3:用列名字作为参数,第二次尝试】
ALTER PROC GetSortedShippers
@ColName AS sysname
AS
SELECT *
FROM Shippers
ORDER BY
CASE @ColName
WHEN 'ShipperID'
THEN CAST(ShipperID AS varchar(11))
WHEN 'CompanyName'
THEN CompanyName
WHEN 'Phone'
THEN Phone
ELSE NULL
END
现在,假设我们再把三个列名字中的任意一个作为参数调用存储过程,输出结果看起来正确。看起来就象指定的列正确地为查询输出提供了排序标准。但这个表只有三个货主,它们的ID分别是1、2、3。假设我们把更多的货主加入到表,如Listing 4所示(ShipperID列有IDENTITY属性,SQL Server自动为该列生成值)。
【Listing 4:向Shippers表插入一些记录】
INSERT INTO Shippers VALUES('Shipper4', '(111) 222-9999')
INSERT INTO Shippers VALUES('Shipper5', '(111) 222-8888')
INSERT INTO Shippers VALUES('Shipper6', '(111) 222-7777')
INSERT INTO Shippers VALUES('Shipper7', '(111) 222-6666')
INSERT INTO Shippers VALUES('Shipper8', '(111) 222-5555')
INSERT INTO Shippers VALUES('Shipper9', '(111) 222-4444')
INSERT INTO Shippers VALUES('Shipper10', '(111) 222-3333')
现在调用存储过程,指定ShipperID作为排序列:
EXEC GetSortedShippers 'ShipperID'
表一显示了存储过程的输出。ShipperID等于10的记录位置错误,因为这个存储过程的排序输出是字符排序,而不是整数排序。按照字符排序时,10排列在2的前面,因为10的开始字符是1。
表一:记录排序错误的查询结果
ShipperID CompanyName Phone
1 Speedy Express (503) 555-9831
10 Shipper10 (111) 222-3333
2 United Package (503) 555-3199
3 Federal Shipping (503) 555-9931
4 Shipper4 (111) 222-9999
5 Shipper5 (111) 222-8888
6 Shipper6 (111) 222-7777
7 Shipper7 (111) 222-6666
8 Shipper8 (111) 222-5555
9 Shipper9 (111) 222-4444
为了解决这个问题,我们可以用前置的0补足ShipperID值,使得ShipperID值都有同样的长度。按照这种方法,基于字符的排序具有和整数排序同样的输出结果。修改后的存储过程如Listing 5所示。十个0被置于ShipperID的绝对值之前,而在结果中,代码只是使用最右边的10个字符。SIGN函数确定在正数的前面加上加号(+)前缀,还是在负数的前面加上负号(-)前缀。按照这种方法,输出结果总是有11个字符,包含一个“+”或“-”字符、前导的字符0以及ShipperID的绝对值。
【Listing 5:用列名字作为参数,第三次尝试】
ALTER PROC GetSortedShippers
@ColName AS sysname
AS
SELECT *
FROM Shippers
ORDER BY
CASE @ColName
WHEN 'ShipperID' THEN CASE SIGN(ShipperID)
WHEN -1 THEN '-'
WHEN 0 THEN '+'
WHEN 1 THEN '+'
ELSE NULL
END +
RIGHT(REPLICATE('0', 10) +
CAST(ABS(ShipperID) AS varchar(10)), 10)
WHEN 'CompanyName' THEN CompanyName
WHEN 'Phone' THEN Phone
ELSE NULL
END
如果ShipperID的值都是正数,加上符号前缀就没有必要,但为了让方案适用于尽可能多的范围,本例加上了符号前缀。排序时“-”在“+”的前面,所以它可以用于正、负数混杂排序的情况。
现在,如果我们用任意三个列名字之一作为参数调用存储过程,存储过程都能够正确地返回结果。Richard Romley提出了一种巧妙的处理方法,如Listing 6所示。它不再要求我们搞清楚可能涉及的列数据类型。这种方法把ORDER BY子句分成三个独立的CASE表达式,每一个表达式处理一个不同的列,避免了由于CASE只返回一种特定数据类型的能力而导致的问题。
【Listing 6:用列名字作为参数,Romley提出的方法】
ALTER PROC GetSortedShippers
@ColName AS sysname
AS
SELECT *
FROM Shippers
ORDER BY
CASE @ColName WHEN 'ShipperID'
THEN ShipperID ELSE NULL END,
CASE @ColName WHEN 'CompanyName'
THEN CompanyName ELSE NULL END,
CASE @ColName WHEN 'Phone'
THEN Phone ELSE NULL END
按照这种方法编写代码,SQL Server能够为每一个CASE表达式返回恰当的数据类型,而且无需进行数据类型转换。但应该注意的是,只有当指定的列不需要进行计算时,索引才能够优化排序操作。
三、用列号作为参数
就象第一个方案所显示地那样,你也许更喜欢用列的编号作为参数,而不是使用列的名字(列的编号即一个代表你想要作为排序依据的列的数字)。这种方法的基本思想与使用列名字作为参数的思想一样:CASE表达式根据指定的列号确定使用哪一个列进行排序。Listing 7显示了修改后的GetSortedShippers存储过程。
【Listing 7:用列号作为参数】
ALTER PROC GetSortedShippers
@ColNumber AS int
AS
SELECT *
FROM Shippers
ORDER BY
CASE @ColNumber
WHEN 1 THEN CASE SIGN(ShipperID)
WHEN -1 THEN '-'
WHEN 0 THEN '+'
WHEN 1 THEN '+'
ELSE NULL
END +
RIGHT(REPLICATE('0', 10) +
CAST(ABS(ShipperID) AS varchar(10)), 10)
WHEN 2 THEN CompanyName
WHEN 3 THEN Phone
ELSE NULL
END
当然,在这里你也可以使用Richard的方法,避免ORDER BY子句中列数据类型带来的问题。如果要根据ShipperID排序输出,你可以按照下面的方式调用修改后的GetSortedShippers存储过程:
EXEC GetSortedShippers 1
四、动态执行
使用动态执行技术,我们能够更轻松地编写出GetSortedShippers存储过程。使用这种方法时,我们只需动态地构造出SELECT语句,然后用EXEC()命令执行这个SELECT语句。假设传递给存储过程的参数是列的名字,存储过程可以大大缩短:
ALTER PROC GetSortedShippers
@ColName AS sysname
AS
EXEC('SELECT * FROM Shippers ORDER BY ' +
@ColName)
在SQL Server 2000和7.0中,你可以用系统存储过程sp_ExecuteSQL替代Exec()命令。BOL说明了使用sp_ExecuteSQL比使用Exec()命令更有利的地方。一般地,如果满足以下三个条件,你能够在不授予存储过程所涉及对象权限的情况下,授予执行存储过程的权限:首先,只使用Data Manipulation Language(DML)语言(即SELECT,INSERT,UPDATE,DELETE);其次,所有被引用的对象都有与存储过程同样的所有者;第三,没有使用动态命令。
上面的存储过程不能满足第三个条件。在这种情况下,你必须为所有需要使用存储过程的用户和组显式地授予Shippers表的SELECT权限。如果这一点可以接受的话,一切不存在问题。类似地,你可以修改存储过程,使它接受一个列号参数,如Listing 8所示。
【Listing 8:用列号作为参数,动态执行(代码较长的方法)】
ALTER PROC GetSortedShippers
@ColNumber AS int
AS
DECLARE @cmd AS varchar(8000)
SET @cmd = 'SELECT * FROM Shippers ORDER BY ' +
CASE @ColNumber
WHEN 1 THEN 'ShipperID'
WHEN 2 THEN 'CompanyName'
WHEN 3 THEN 'Phone'
ELSE 'NULL'
END
EXEC(@cmd)
注意,当你使用了函数时,你应该在一个变量而不是EXEC()命令内构造SELECT语句。此时,CASE表达式动态地确定使用哪一个列。还有一种更简短的格式,T-SQL允许在ORDER BY子句中指定SELECT清单中列的位置,如Listing 9所示。这种格式遵从了SQL-92标准,但ANSI SQL-99标准不支持这种格式,所以最好不要使用这种格式。
【Listing 9:列号作为参数,动态执行(代码较短的方法)】
ALTER PROC GetSortedShippers
@ColNumber AS int
AS
DECLARE @cmd AS varchar(8000)
SET @cmd = 'SELECT * FROM Shippers ORDER BY ' + CAST(@ColNumber AS varchar(4))
EXEC(@cmd)
五、用户定义函数
如果你使用的是SQL Server 2000,想要编写一个用户定义的函数(UDF),这个用户定义函数接受列的名字或编号为参数、返回排序的结果集,Listing 10显示了大多数程序员当成第一选择的方法。
【Listing 10:列名字作为参数,使用UDF】
CREATE FUNCTION ufn_GetSortedShippers
(
@ColName AS sysname
)
RETURNS TABLE
AS
RETURN
SELECT *
FROM Shippers
ORDER BY
CASE @ColName
WHEN 'ShipperID' THEN CASE SIGN(ShipperID)
WHEN -1 THEN '-'
WHEN 0 THEN '+'
WHEN 1 THEN '+'
ELSE NULL
END +
RIGHT(REPLICATE('0', 10) +
CAST(ABS(ShipperID) AS
varchar(10)), 10)
WHEN 'CompanyName' THEN CompanyName
WHEN 'Phone' THEN Phone
ELSE NULL
END
但是,SQL Server不接受这个函数,它将返回如下错误信息:
Server: Msg 1033, Level 15, State 1, Procedure ufn_GetSortedShippers,
Line 24
The ORDER BY clause is invalid in views, inline functions, and
subqueries, unless TOP is also specified.
注意错误信息中的“unless”。SQL Server 2000不允许在视图、嵌入式UDF、子查询中出现ORDER BY子句,因为它们都应该返回一个表,表不能指定行的次序。然而,如果使用了TOP关键词,ORDER BY子句将帮助确定查询所返回的行。因此,如果指定了TOP,你还可以同时指定ORDER BY。由于在带有TOP的UDF中允许使用ORDER BY子句,你可以使用一个技巧:把“SELECT *”替换成“SELECT TOP 100 PERCENT *”。这样,你就能够成功地构造出一个接受列名字或编号为参数、返回排序结果的函数。
新构造的函数可以按照如下方式调用:
SELECT * FROM ufn_GetSortedShippers('ShipperID')
现在,你已经了解了几种用参数确定查询输出中记录次序的方法。在编写那些允许用户指定查询结果排序标准的列的应用程序时,你可以使用本文介绍的各种技术,用列名字或编号作为参数,构造出使用CASE表达式和动态执行能力的各种方案。
……