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