x

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

more ▼

asked Mar 07, 2013 at 08:36 PM in Default

avatar image

jpatchak
290 3 5 8

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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' 

more ▼

answered Mar 07, 2013 at 09:10 PM

avatar image

jpatchak
290 3 5 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2188
x57
x13

asked: Mar 07, 2013 at 08:36 PM

Seen: 816 times

Last Updated: Mar 07, 2013 at 09:10 PM

Copyright 2017 Redgate Software. Privacy Policy