is there a better way to find rows with all column with values?

Question

I need to find rows in resultsets that have every column without null.

these resultsets got variable number of columns.

currently, the only way that I can think of is creating a view on each resultset and filter this way:

 select field1, field2, field3, field4, ...
 from "huge query"
 where field1 is not null and  
       field2 is not null and  
       field3 is not null and  
       field4 is not null and
       ...  is not null

is there a better way to do this in a storeproc/function in sql server or in .net code (c# or vb.net) ?

and what about doing something like

 select field1, field2, field3, field4, ...
 from "huge query"
 (return to .net apps or insert into #temptable)

and then in a storeproc/function or .net code(c# / vb.net) loop through all rows / columns and flag or remove every row that got any null?

I'm talking about easily over 50 different kind of resultsets and it might grow over time, so i'm looking for a generic / easily maintainable way

Answer 1

Your method is not pretty but I actually think it will perform the best. Another way is to do

WHERE (field1 + field2 + field3 + field4) IS NOT NULL

Answer 2

Since nulls propagate, if they're all the same datatype, try

where colA + ColB + ColC, etc Is Not Null

If they're not, then convert them all (the ones that are not already a string) to char first, and then concatenate them.

where Str(ColA) + Str(ColB) + Str(ColC), etc Is Not Null

Answer 3

If field1, field2, field2... fieldX are string, you may want to try :

select field1, field2, field3, field4, ...
from "huge query"
where field1 + field2 + field3 + ... + fieldX is not null