Click here to Skip to main content
14,882,036 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I have a stored procedure (Sql server 2008)
SQL
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


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

VB
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


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?
Posted
Comments
Herman<T>.Instance 7-Feb-12 6:52am
   
and what if you use Coalesce(L.DateTimeExpires, @now) ?
It is strange behaviour indeed! I would expect it should work too.
_Maxxx_ 7-Feb-12 17:20pm
   
I haven't tried coalesce - I'm not interested in fixing the issue (I have fixed it) but working out why it is not working

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
   
Comments
Sander Rossel 7-Feb-12 17:54pm
   
Congrats! Such issues can take you many hours of frustration to find out, and then when you finally got it it's all so obvious :laugh:
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...
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900