question

jpatchak avatar image
jpatchak asked

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-2008query-resultsmanagement-studio
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
jpatchak avatar image
jpatchak answered
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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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