Check if columns are NULL or contains NULL in table. - MS SQL Server

Question

Following is my table (TestTable) where Column_3 is NULL.

Column_1 Column_2 Column_3
-------- -------- --------
1        2        NULL
1        3        NULL
5        6        NULL

As per functionality, user can select one or more columns. For example, if user selects Column_3 & Column_2 where Column_3 is NULL. I want to tell user that Column_3 is NULL.

Query : Works for single column

if exists(select * from TestTable where Column_3 is null)
    print 'Yes'
else
    print 'No'

Result :

Yes

Query : For multiple Columns (not working)

declare @columns nvarchar(max), @tableName nvarchar(max), @query nvarchar(max)

set @columns = 'Column_3, Column_2'
set @tableName = 'TestTable'
set @query = 'select * from (select ' + @columns + ' from ' + @tableName + ') as Result'
print @query
EXEC SP_EXECUTESQL @query

Here

@query = select * from (select Column_3, Column_2 from TestTable) as Result

Above query gives me result for those two columns. I'm not sure how can I check NULL in this query for multiple columns. If I add IS NULL (like I did for single column) after or before last parenthesis it gives me Incorrect syntax near the keyword 'is'. error. How can I achieve my goal in this situation?

Answer 1

Two Solutions (Column is All NULLs, Column Contains Some NULLs)

I have slightly altered your original example in order to provide two solutions:

Column_1 Column_2 Column_3
-------- -------- --------
1        2        NULL
1        NULL     NULL
5        6        NULL

First, test for NULLs and count them:

select 
    sum(case when Column_1 is null then 1 else 0 end) as Column_1, 
    sum(case when Column_2 is null then 1 else 0 end) as Column_2, 
    sum(case when Column_3 is null then 1 else 0 end) as Column_3,
from TestTable 

Yields a count of NULLs:

Column_1  Column_2  Column_3
0         1         3

Where the result is 0, there are no NULLs.

Second, let's count the non-NULLs:

select 
    sum(case when Column_1 is null then 0 else 1 end) as Column_1, 
    sum(case when Column_2 is null then 0 else 1 end) as Column_2, 
    sum(case when Column_3 is null then 0 else 1 end) as Column_3,
from TestTable

...But because we're counting non-NULLs here, this can be simplified to:

select 
    count(Column_1) as Column_1, 
    count(Column_2) as Column_2, 
    count(Column_3) as Column_3,
from TestTable

Either one yields:

Column_1  Column_2  Column_3
3         2         0

Where the result is 0, the column is entirely made up of NULLs.

If you only need to check a given column, then TOP 1 is quicker because it should stop at the first hit:

select count(*) from (select top 1 'There is at least one NULL' AS note from TestTable where Column_3 is NULL) a

0 = There are no NULLs, 1 = There is at least one NULL

SELECT COUNT(*) FROM (SELECT TOP 1 'There is at least one non-NULL' AS note FROM sat_data_active_season_group WHERE season_group IS NOT NULL) a

0 = They are all NULL, 1 = There is at least one non-NULL

I hope this helps.

Answer 2

we can check with the help of IN like

...WHERE NULL IN (Column_2, Column_3)

from your comment Well the multiple column will be Column_3, Column_2 in format might be this is helpful for you

select * from (select Column_3, Column_2 from @temp where null in (Column_3, Column_2)) as Result