Catching null warnings in aggregate functions in sql

Question

How does one use the debugger in sql 2008 / 2012 to catch null values in records?

See:

drop table abc

create table abc(
a  int
)
go 
insert into abc values(1)
insert into abc values(null)
insert into abc values(2)

select max(a) from abc

(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.

Now this can be rectifed by doing:

SELECT max(isNull(a,0)) FROM abc

which is fine, until I come to to 200 line queries with several levels of nesting,and a result set of 2000 odd records. -- And then have no clue which column is throwing the warning.

How do I add conditional breakpoints ( or break on warning ) in the SQL debugger? ( if it is even possible )

Answer 1

Part 1: About aggregate warnings...
Considering your several levels nesting I am afraid there is no straightforward way of seeing which records trigger those warnings.

I think your best shot would be to remove each aggregate function, one at a time, from the SELECT part of the top-level statement and run query so you can see which aggregate is causing warnings at the top level (if any)

After that you should move on to nested queries and move each sub-query that feeds the top-level aggregates to a separate window and run it there, check for warnings. You should repeat this for additional levels of nesting to find out what actually causes the warnings.

You can employ the following method also.

Part 2:About conditional breakpoints...
For the sake of debugging, you move each of you nested tables out and put its data to a temp table. After that you check for null values in that temp table. You set a breakpoint in an IF statement. I believe this is the best thing close to a conditional breakpoint. (IF clause can be altered to build other conditions)

Here is a solid example,
Instead of this:

SELECT A.col1, A.col2, SUM(A.col3) as col3
FROM (SELECT X as col1, Y as col2, MAX(Z) as col3 
      FROM (SELECT A as X, B as Y, MIN(C) as Z
            FROM myTableC
           ) as myTableB
     ) as myTableA

do this:

SELECT A as X, B as Y, MIN(C) as Z
INTO #tempTableC
FROM myTableC

IF EXISTS (SELECT * 
           FROM #tempTableC
           WHERE A IS NULL ) BEGIN
     SELECT 'A' --- Breakpoint here
END


SELECT X as col1, Y as col2, MAX(Z) as col3
INTO #tempTableB
FROM #tempTableC

IF EXISTS (SELECT *  
           FROM #tempTableB
           WHERE X IS NULL ) BEGIN
     SELECT 'B' --- Breakpoint here
END

SELECT col1, col2, SUM(col3) as col3 
FROM #tempTableB as myTableA