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
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...
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?