sp_executesql返回的结果不是直接查询(sp_executesql returning different results than direct query)

以下两个查询返回不同的结果。 我理解这与日期的时间部分处理方式有什么不同，但为什么这样做呢？

// 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

UPDATE

如果我将传递给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

The following two queries are returning different results. I understand the difference has to do with the way the time portions of the dates are being handled, but why is it working this way?

// 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

UPDATE

If I change the types of the parameters passed to sp_executesql to 'date' instead of 'datetime', the results are the same.

// 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

最满意答案

数据类型优先顺序是将表中的数据作为日期开始，并将其作为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

Data type precedence is taking the data in your table, which starts as a date, and compares it as a datetime2(7). So your dynamic SQL version is actually running this:

WHERE column_as_datetime2 >= @parameter_as_datetime2

So, since 2013-11-01 00:00:00.0000000 is not greater than or equal to 2013-11-01 04:00:00.0000000, the rows from November 1st are left out.

The most practical solution is to use DATE parameters (preferred, since the parameters should match the underlying data type, after all), and/or stop passing time values along with them. Try these:

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';

Results:

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

更多推荐