奇怪的SQL行为-还是仅我一个人? [英] Weird SQL Behaviour - or is it just me?

问题描述

我有一个存储过程(Sql server 2008)

I have a stored procedure (Sql server 2008)

BEGIN TRY
  DECLARE @result Int
  DECLARE @test Int
  Declare @now datetime
  set @now = GETDATE()

   Select @test = COUNT(*)
   From dbo.Table1 as M
   Inner join dbo.Table2 as L on M.SomeKey = L.SomeKey
   where M.Id = @Id
   And IsNull(L.DateTimeExpires , @now) >= @Now

 if @test = 0 -- no valid record found
   set @result = 1
else
    set @result = 0

END TRY	
BEGIN CATCH
  set @result = 2
END CATCH
	
select @Result

我从VB.Net调用它如下...

I am calling it from a bit of VB.Net as follows...

Dim db As Database = DatabaseFactory.CreateDatabase("MyDatabase")
      Dim dbCommand As DbCommand = db.GetStoredProcCommand("MyStoredProc")
      db.AddInParameter(dbCommand, "Id", DbType.String, computerId)
      Dim OK As Integer = db.ExecuteScalar(dbCommand)
      Return OK

WCF服务的服务器端是哪个(我认为这并不重要).

如果我运行此命令，它将返回"1"-即找不到任何记录.
如果我在调试中运行它，请等待例如5秒钟，然后再调用ExecuteScalar，它会返回"0"

这是一致的(重新启动等没有区别.

所以-我将其范围缩小到必须与SQL有关的事实
And IsNull(L.DateTimeExpires , @now) >= @Now

如果我将这行替换为

And (L.DateTimeExpires is Null or L.dateTimeExpires > @Now)

那么无论延迟如何，它似乎都可以正常工作.

相应记录中的DateTimeExpires列为null.

我确定我只是缺少明显的东西-但如果我能看到它，我会感到震惊...

Which is (and I don''t think this matters a jot) on the server side of a WCF service.

If I run this, it returns ''1'' - i.e. it finds no records.
If I run it in debug, wait for, say, 5 seconds before calling the ExecuteScalar, it returns ''0''

This is consistent (rebooting etc. makes no difference.

So - I narrowed it down to the fact that it must be something to do with the SQL
And IsNull(L.DateTimeExpires , @now) >= @Now

If I replace this line with

And (L.DateTimeExpires is Null or L.dateTimeExpires > @Now)

then it seems to work fine regardless of any delays.

The DateTimeExpires column in the appropriate record is null.

I am sure I am just missing something obvious - but I''m blowed if i can see it...

What''s going on?

推荐答案

解决了！！！

好的，因此数据库上的DateTimeExpires列是smalldatetime，但是SP中的@now是DateTime

IsNull(DateTimeExpires，@now)显然返回一个smalldatetime-但正在将> =与日期时间进行比较

所以@now<> @now

Solved it!!!

OK, so the DateTimeExpires column on the DB is a smalldatetime, but the @now in the SP was a DateTime

IsNull(DateTimeExpires, @now) evidently returns a smalldatetime - but is comparing >= to a datetime

And so @now <> @now

无法看到为什么它不起作用.您的修复程序不正确(或者您的初始代码是)...
And IsNull(L.DateTimeExpires , @now) >= @Now应该找到DateTimeExpires为null的所有记录是@now或大于@now.
And (L.DateTimeExpires is Null or L.dateTimeExpires > @Now)应该只查找DateTimeExpires为null或大于@now的所有记录(如果DateTimeExpires为@now，则找不到该记录).
您还考虑到GETDATE()仍将小时，分钟和秒设置为@now变量的事实吗?它可能什么都没有，但是你永远不知道...
您确定在软件和调试中使用相同的ID吗?您是否使用相同的数据库?您在正确的数据库中使用正确的ID吗?可能看起来很明显，但是它们是常见的错误:)
只是提出一些建议，一切似乎都很好...

Can''t see why it won''t work. Your fix is incorrect though (or your initial code was)...
And IsNull(L.DateTimeExpires , @now) >= @Now should find all record where DateTimeExpires is null is @now or is greater than @now.
And (L.DateTimeExpires is Null or L.dateTimeExpires > @Now) should only find all records where DateTimeExpires is null or is greater than @now (it will not find the record if the DateTimeExpires is @now).
Also do you account for the fact that GETDATE() still sets hours, minutes and seconds to the @now variable? It probably means nothing, but you never know...
Are you sure you are using the same ID in your software and in debug? Are you using the same database? Are you using the correct ID in the correct database? May seem obvious, but they are common mistakes :)
Just making some suggestions, all seems fine...

这篇关于奇怪的SQL行为-还是仅我一个人?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！