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
OR