More loopy looism

Here's a little thing I was forced to realise after a far too high profile site when slightly wrong (in a far too big way).

Looping a query inside a loop of another query changes the way you can refer to the outer-loops current row. Running the following code demonstrates this:

<cfscript>
  qry_foo = QueryNew('foo');
  QueryAddRow(qry_foo, 3);
  for(i=1; i LTE 3; i=i+1){
    QuerySetCell(qry_foo,'foo',"foo #i#",i);
  }
  qry_bar = QueryNew("bar");
  QueryAddRow(qry_bar, 10);
  for(i=1; i LTE 10; i=i+1){
    QuerySetCell(qry_bar,'bar',"bar #i#",i);
  }

</cfscript>
<cfoutput>
  <ul>
    <cfloop query="qry_foo">
      <li>#qry_foo.foo#
        <ul>
          <cfloop query="qry_bar">
            <li>#qry_foo.foo#</li>
          </cfloop>
        </ul>
      </li>
    </cfloop>
  </ul>
</cfoutput>

The problem is that you cannot access the current row of the qry_foo query when inside the loop of qry_bar in this way. Instead, you would have to use:

qry_foo.foo[qry_foo.currentRow]

Does anyone else find this bizarre?

SQL Date Comparison Gotcha

Just a little thing to remind me to use DateDiff() instead of regular operators when comparing dates. Here's why:

Say you have a deadline date stored in a db and you need to see if today is passed the deadline. If you do this:

DECLARE @deadline smalldatetime
SET @deadline = '2007-12-04'

IF(GetDate() <= @deadline)
PRINT 'Deadline not passed'
ELSE
PRINT 'Deadline passed'

You will get 'Deadline passed' if the deadline is today which is not as you might expect. The reason for this is that GetDate() also returns the time and the operator takes this into consideration, i.e. the following is true 2007-12-04 16:19 > 2007-12-04 00:00.

So, instead, when comparing the difference in days one should always use:

IF(DateDiff(d, GetDate(), @deadline) >= 0)
PRINT 'Deadline not passed'
ELSE
PRINT 'Deadline passed'

Incidentally, the same is true for ColdFusion and the DateDiff ColdFusion function works in the same way.