SQL Query Returns no Results...Sometimes

Question

question

jpatchak asked Mar 7, '13

SQL Query Returns no Results...Sometimes

OK I am beginning to feel like I'm losing my mind. I have a well-formed query that I'm executing in SSMS. If I run it, say, 20 times in a row, 10 times it produces 40k+ records and 10 times it will produce no records (although SSMS says that the query completed succesffully). Does anyone have any idea what could possible cause this behavior? Here is my code. The 2 views referenced in this query function fine and are used all over in other queries, so we would know if there was something up with those views:


SELECT s.field1, s.field2
FROM view1 s
INNER JOIN table1 rp
ON s.primaryKey=rp.foreignKey
AND rp.bitField=1
AND ISNULL(rp.startDate, GETDATE())<=GETDATE()
AND ISNULL(rp.endDate, GETDATE())>=GETDATE()
INNER JOIN table2 p
ON rp.foreignKey=p.primaryKey
INNER JOIN table3 i
ON p.foreignKey=i.primaryKey
INNER JOIN view2 con
ON con.foreignKey=s.primaryKey
AND con.bitField=1
WHERE s.active=1

sql-server-2008 query-results management-studio

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2013-03-07T21:10:50Z

jpatchak answered Mar 7, '13

I figured this out - GETDATE() is not evaluated at compile time; it's evaluated at runtime, so the lines that say

ISNULL(rp.startDate, GETATE())<=GETDATE()

are the culprits as demonstrated by this script:


WHILE DATEDIFF(ms, GETDATE() , GETDATE()) = 0 PRINT 'This will not run in an infinite loop'

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question