I have got a table with measurement data in SQL Server 2005, one value per person and year, if available. My TSQL code brings these values inside a loop and procedures them:

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

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

How do you differentiate between these cases effectively? Both (ii) and (iii) can lead to @val being NULL, so using the current code, they cannot be classified...

Thanks a lot for just about any hints, wwwald

If use a value that measurement can't ever 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

Possibly check @@ROWCOUNT? However, this can be a tiny bit dangerous, since you need to be certain to avoid every other procedures before you decide to check @@ROWCOUNT (it up-to-date after most procedures).

Alternatively, read another column, such as the primary key:

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

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

I question your reason for looping whatsoever? Looping is really a bad factor in SQL Server because it is a performance killer. The majority of things possess a better set-based solution. Possibly your condition includes a better solution should you inform us your work informed besides setting the need for a flexible.

Well, it's a little of the 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 continues to be -9999 then there is no row, or null when the measurement was null.