数据库查询结果的动态排序(5)
五、用户定义函数
如果你使用的是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表达式和动态执行能力的各种方案。