Differentiate empty from NULL query results

Question

I've got a table with measurement data in SQL Server 2005, one value per person and year, if available. My TSQL code fetches these values in a loop and processes them:

...
SET @val = (SELECT measurement FROM tbl_data WHERE persid = @curpersid AND yr = @curyear)
...

Now, for a certain person and year, the table can contain (i) a valid measurement, (ii) a NULL value or (iii) no corresponding row at all.

How do I differentiate between these cases efficiently? Both (ii) and (iii) will result in @val being NULL, so with the current code, they can't be differentiated...

Thanks a bunch for any hints, wwwald

Answer 1

Perhaps check @@ROWCOUNT? However, this is a little bit risky, as you need to be sure not to do any other operations before you check @@ROWCOUNT (it updated after most operations).

Alternatively, read another column, like the primary key:

SELECT @val = measurement, @id = id
FROM tbl_data WHERE persid = @curpersid AND yr = @curyear

now check @id - if it is NULL, there was no row.

Answer 2

If you can use a value that measurement can never take, COALESCE() can used.

SET @val = (SELECT COALESCE(measurement, someValueThatDoesNotOccur) FROM tbl_data WHERE persid = @curpersid AND yr = @curyear)

No row: @val == null

measurement was NULL: @val equals somevalue

else a vlaid measurement

Answer 3

Well, it's a bit of a hack, but:

SET @val = -99999  -- or some other value that will never occur in the table
SELECT @val = measurement FROM tbl_data WHERE persid = @curpersid AND yr = @curyear

Now, if @val is still -9999 then there was no row, or null if the measurement was null.

Answer 4

I question why you are looping at all? Looping is a bad thing in SQL Server as it is a performance killer. Most things have a better set-based solution. Perhaps your problem has a better solution if you tell us what you are doing in the loop besides setting the value of a variable.