Order of execution in logical expressions

GlacierI'm a little confused about order of execution in logical expressions.

In a statement like this:

SELECT * FROM myTable
WHERE PK_ID > 0 OR
(PK_ID <=0 AND 1/0 > 5)

apparently statement compilation jumps in and detects the divide by zero error, even though that clause would never be executed, because all PK_IDs in myTable are in fact > 0.

On the other hand, if I "hide" the divide by zero, then Transact-SQL seems to use what the .NET world calls "partial statement evaluation" (we called it something else in the old COBOL through Pascal days):

SELECT * FROM myTable
WHERE PK_ID > 0 OR
(PK_ID <=0 AND 1/myFunction(5) > 5)

In this case, myFunction(5) would return zero if called, but as this is apparently out of reach of the statement compiler, the query runs fine and returns all rows.

This question arose because of a sub-query in an UPDATE TRIGGER:

....

IF NOT UPDATE(fieldname) OR
(SELECT DISTINCT fieldname FROM INSERTED) IS NULL)

For a multiple-row update, at execution time this statement returned an error about multiple rows being returned from a sub-query.

However, if the statement is being executed using "partial statement evaluation", then the only time the second clause should be executed is if UPDATE(fieldname) is TRUE.

When UPDATE(fieldname) is TRUE there can only be one value for fieldname, the value supplied in the UPDATE statement.

Therefore the sub-query error is inappropriate.

(Incidentally, I "fixed" it by replacing DISTINCT with TOP 1, even though DISTINCT could not have returned more than one value in this case).

I'm wondering if somebody can help me understand better when Transact-SQL will and will not execute logical clauses using AND and OR, as it seems unpredictable to me, and in the context of a WHERE clause, I don't have the luxury of using nested IF statements as one has to do in at least some members of the VB family (VBScript, VBA, VB, VB.Net):

Thanks!

---Wayne

JPEG image

Relevant Pages

  • Re: COBOLs Influence on C
    ... Robert's reply seems to "assume" an IBM mainframe environment. ... learn of missing external names either at link or execution time. ... compilation and before execution. ...
    (comp.lang.cobol)
  • Cannot open a log file of specified name
    ... I have a DTS package called 'MASTER' which performs 8 execute DTS package ... The execution of the following DTS Package succeeded: ... Total Step Execution Time: 2.375 seconds ... Progress count in Step: 0 ...
    (microsoft.public.sqlserver.dts)
  • Re: Embedded Basic interpreter recommendations?
    ... Code size and execution time should be no problem, ... but the rest is important for interactive development on the target ... And do all of that in a relatively small part of the flash -- which, ...
    (comp.arch.embedded)
  • Re: flash and external ram timing on TI 2812
    ... Would someone be able to explain me how to calculate the execution time of ... One routine should configure the settings and timing for the ...
    (comp.dsp)
  • Re: Model control-flow graphs
    ... let's assume I know the execution time for each ... I'd like to find the longest path within my graph ... The maximal execution time is typically discovered using Integer Linear Programming applied to the extended CFG -- the Implicit Path Enumeration technique; see the survey for references. ... Niklas Holsti ...
    (comp.arch.embedded)
Loading