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
@@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
@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.