How to determine of an SQL table row contains values other than NULL

Question

This is the problem:

I have a table with unknown number of columns. all the columns are real.

Assuming there is only one row in that table, I need a method to determine if there is a value other than NULL in that table/row.

I don't know the number of columns nor their name at run-time (and don't want to use c cursor)

SQL Server 2005

I appreciate your help.

Answer 1

Sounds like you're doing some kind of a settings/properties table, based on the fact that you know you only have 1 row in it. This is the wrong way to do it, if you need to have dynamic properties; instead have a table with 2 columns: option and value. Then for each dynamic property, you'll store one row.

Answer 2

Here's one way - CHECKSUM() returns no value if all values in the row are NULL:

create table #t (col1 real, col2 real, col3 real)
select checksum(*) from #t
if @@rowcount = 0
    print 'All values are NULL'
else
    print 'Non-NULL value(s) found'
drop table #t

On the other hand, I don't really know if this is what you're doing: a "temporary table built in memory" sounds like something you're managing yourself. With more information about what you're trying to achieve, we might be able to suggest a better solution.

And by the way, there is nothing wrong with a single-row table for storing settings. It has the big advantage that each setting has a separate data type, can have CHECK constraints etc.