none
由数据库排序差异引起的错误 RRS feed

  • 问题

  • 小弟遇到一个奇怪的sql server问题,请各路高手指点:

    在SQL SERVER 2005,语言:英文,排序:Chinese_PRC_Stroke_90_CI_AS的数据库环境中,执行下面语句:

    EXEC sp_executesql N'SELECT Bonus, EmpId, PayMonth, PayYear
    FROM HSPayrollUploadRecord
    WHERE (PayYear 
    = @PayYear) AND (PayMonth = @PayMonth) AND (EmpId = @EmpId)',
        N'@PayYear sql_variant,@PayMonth sql_variant,@EmpId sql_variant',
        @PayYear 
    = '2010', @PayMonth = '7', @EmpId = 'test02'


    查询结果为:
    (0 行受影响)

    但是如果这样写:SELECT Bonus, EmpId, PayMonth, PayYear
    FROM HSPayrollUploadRecord
    WHERE (PayYear = '2010') AND (PayMonth = '7') AND (EmpId = 'test02')

    查询结果为:

    309.0000 test02 7 2010     

     (1 行受影响)

    请问第一种写法为什么查询不出数据来?真诚谢谢了!!

    2010年9月17日 6:02

答案

  • From books online:

    When sql_variant values of the char, varchar, nchar, or nvarchar data types are compared, they are evaluated based on the following criteria: LCID, LCID version, comparison flags, and sort ID. Each of these criteria are compared as integer values, and in the order listed.

    2010年9月17日 13:21
  • 数据类型转换导致的问题, 不是排序规则引起的问题.

    第一种写法, sql_variant 的优先级最高, 所有的数据都转换为 sql_variant 后做比较, 当你的表中列类型不是 varchar 的时候, 它转换为 sql_variant 的结果与 varchar 存储到 sql_varint 中的结果不一样, 导致查不到结果

    第二种写法, 你用的是基础类型, 数据转换会根据你的列和常量的数据类型判断, 是该向那种类型转换, 这种转换只要不丢失(截断)数据, 一般都能够匹配成功的.

     

    数据类型优先级参考下面的链接

    http://msdn.microsoft.com/zh-cn/library/ms190309.aspx

    2010年9月19日 4:59
  • 下面的测试可以重现我说的问题

    create table #tb(
    	y1 varchar(1000),
    	y2 int
    );
    insert #tb values(2010, 2010)
    ;
    EXEC sp_executesql N'
    SELECT 
    	y1
    from #tb
    where y1 = @PayYear
    ;
    
    SELECT 
    	y2
    from #tb
    where y2 = @PayYear
    ',
      N'@PayYear sql_variant,@PayMonth sql_variant,@EmpId sql_variant',
      @PayYear = '2010', @PayMonth = '7', @EmpId = 'test02'
    go
    drop table #tb
    
    
    • 已标记为答案 jizhong 2010年10月25日 7:52
    2010年9月19日 4:59

全部回复

  • @PayYear sql_variant,@PayMonth sql_variant,@EmpId sql_variant',

    尝试指定正确的字段类型试试。

     


    family as water
    2010年9月17日 7:27
  • 如果将三个参数的类型变为varchar或者nvarchar,那么也是可以查询出结果的。由于本人水平所限,搞不懂为什么在sql_variant型参数下并且在Chinese_PRC_Stroke_90_CI_AS排序规则中查询不出数据来。

    2010年9月17日 7:30
  • From books online:

    When sql_variant values of the char, varchar, nchar, or nvarchar data types are compared, they are evaluated based on the following criteria: LCID, LCID version, comparison flags, and sort ID. Each of these criteria are compared as integer values, and in the order listed.

    2010年9月17日 13:21
  • 思路应该没错,应该错在语法上。使用EXEC sp_executesql 执行Sql语句后面应该是字符串.而其中 @EmpId 虽然是变量属于字符串类型但程序不知道,你应该在其中使用连接字符串拼接起来 如:'''+@Empid+'''

    【煜诚海参】http://jinweb.taobao.com
    2010年9月18日 13:59
  • 数据类型转换导致的问题, 不是排序规则引起的问题.

    第一种写法, sql_variant 的优先级最高, 所有的数据都转换为 sql_variant 后做比较, 当你的表中列类型不是 varchar 的时候, 它转换为 sql_variant 的结果与 varchar 存储到 sql_varint 中的结果不一样, 导致查不到结果

    第二种写法, 你用的是基础类型, 数据转换会根据你的列和常量的数据类型判断, 是该向那种类型转换, 这种转换只要不丢失(截断)数据, 一般都能够匹配成功的.

     

    数据类型优先级参考下面的链接

    http://msdn.microsoft.com/zh-cn/library/ms190309.aspx

    2010年9月19日 4:59
  • 下面的测试可以重现我说的问题

    create table #tb(
    	y1 varchar(1000),
    	y2 int
    );
    insert #tb values(2010, 2010)
    ;
    EXEC sp_executesql N'
    SELECT 
    	y1
    from #tb
    where y1 = @PayYear
    ;
    
    SELECT 
    	y2
    from #tb
    where y2 = @PayYear
    ',
      N'@PayYear sql_variant,@PayMonth sql_variant,@EmpId sql_variant',
      @PayYear = '2010', @PayMonth = '7', @EmpId = 'test02'
    go
    drop table #tb
    
    
    • 已标记为答案 jizhong 2010年10月25日 7:52
    2010年9月19日 4:59
  • 应该根据列的类型去定义变量的数据类型, 这样可以避免风险, 并且保证性能.
    2010年9月19日 5:00