日期：2011-05-02 15:21:00 来源：本站整理
数据库查询后果的动态排序(2)[MSSQL防范]
本文“数据库查询后果的动态排序(2)[MSSQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
2、用列名字作为参数
别的一个挑选是让查询以参数的情势接纳一个列名字.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表达式返回得当的数据范例,并且无需举行数据范例转换.但应当注意的是,只有当指定的列不需求举行计算时,索引才可以优化排序操作.
| 本文地址： | 与您的QQ/BBS好友分享! |
- 好的评价 如果您觉得此文章好，就请您
0%(0) 
- 差的评价 如果您觉得此文章差，就请您
0%(0) 
中查找“数据库查询后果的动态排序(2)”更多相关内容
中查找“数据库查询后果的动态排序(2)”更多相关内容评论内容只代表网友观点，与本站立场无关！
评论摘要(共 0 条，得分 0 分，平均 0 分)
查看完整评论