sql - sp_executesql 返回与直接查询不同的结果

Question

以下两个查询返回不同的结果。我知道差异与处理日期的时间部分的方式有关，但为什么会这样？

// QUERY ONE
select top 3 OrderDate
from Orders
where OrderDate >= '2013-11-01 04:00'
and OrderDate <= '2013-11-30 05:00'
order by OrderDate

// RESULTS
// 2013-11-01
// 2013-11-01
// 2013-11-01

// QUERY TWO
exec sp_executesql
    N'select top 3 OrderDate
      from Orders
      where OrderDate >= @p__linq__0
      and OrderDate <= @p__linq__1
      order by OrderDate',
    N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7)',
    @p__linq__0='2013-11-01T04:00:00',
    @p__linq__1='2013-11-30T05:00:00'

// RESULTS
// 2013-11-02
// 2013-11-02
// 2013-11-02

更新

如果我将传递给 sp_executesql 的参数类型更改为“date”而不是“datetime”，结果是相同的。

// QUERY THREE
exec sp_executesql
N'select top 3 OrderDate
  from Orders
  where OrderDate >= @p__linq__0
  and OrderDate <= @p__linq__1
  order by OrderDate',
N'@p__linq__0 date,@p__linq__1 date',
@p__linq__0='2013-11-01T04:00:00',
@p__linq__1='2013-11-30T05:00:00'

// RESULTS
// 2013-11-01
// 2013-11-01
// 2013-11-01

Answer 1

Data type precedence正在获取表中的数据(以日期开头)，并将其与 datetime2(7) 进行比较。所以你的动态 SQL 版本实际上正在运行:

WHERE column_as_datetime2 >= @parameter_as_datetime2

因此，由于 2013-11-01 00:00:00.0000000 不大于或等于 2013-11-01 04:00:00.0000000 ，11 月 1 日以来的行被忽略。

最实用的解决方案是使用DATE参数(首选，因为参数毕竟应该与底层数据类型匹配)，和/或停止传递时间值。试试这些:

USE tempdb;
GO

CREATE TABLE dbo.Orders(OrderDate DATE);

INSERT dbo.Orders VALUES('2013-11-01'),('2013-11-01'),('2013-11-01'),
  ('2013-11-02'),('2013-11-02'),('2013-11-02');

exec sp_executesql N'select top 3 OrderDate
      from Orders
      where OrderDate >= @p__linq__0
      and OrderDate <= @p__linq__1
      order by OrderDate;
select top 3 OrderDate
      from Orders
      where OrderDate >= @p2
      and OrderDate <= @p3
      order by OrderDate;
select top 3 OrderDate
      from Orders
      where OrderDate >= @p4
      and OrderDate <= @p5
      order by OrderDate;',
    N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),
      @p2 datetime2(7),@p3 datetime2(7),@p4 date,@p5 date',
    @p__linq__0='2013-11-01T04:00:00',
    @p__linq__1='2013-11-30T05:00:00',
    @p2='2013-11-01T00:00:00', -- note no time
    @p3='2013-11-30T00:00:00', -- note no time
    @p4='2013-11-01',
    @p5='2013-11-30';

结果:

OrderDate
----------
2013-11-02
2013-11-02
2013-11-02

OrderDate
----------
2013-11-01
2013-11-01
2013-11-01

OrderDate
----------
2013-11-01
2013-11-01
2013-11-01