CODEWITHSUNDEEP
sqlsql-server
Kanan Mehdizade
Kanan Mehdizade

Reputation: 73

How to create function to count null values of columns in table

I have 100 columns and would like to know count of null values of 100 columns in total in the same table. Have created Function, which lets me to insert but once I start to use the function, it gives me error. 

create function dmt.countOfNullValues(@columnName varchar(50))
returns int 
as
begin 
declare @countOfNull int
set @countOfNull = 'select count(t1.null_values) from (
select case  when ' + @columnName + ' is null then 1 else 0 end as null_values from dmt.TableName) as t1
where t1.null_values = 1'; 
return @countOfNull 
end

Error:

Conversion failed when converting the varchar value 'select count(t1.null_values) from ( select case when N is null then 1 else 0 end as null_values from dmt.co01_ba_model_data) as t1 where t1.null_values = 1' to data type int.

Could you please help to clarify my mistakes?

Upvotes: 0

Views: 374

Answers (2)

sticky bit
sticky bit

Reputation: 37472

If you want to check all columns try and use conditional aggregation.

SELECT count(CASE
               WHEN nmuloc1 IS NULL THEN
                 1
             END) nmuloc1_null_count,
       ...
       count(CASE
               WHEN nmuloc100 IS NULL THEN
                 1
             END) nmuloc100_null_count
       FROM elbat;

Yes, that's tedious to write, but will scan the table only once as opposed to scan it for each column individually. So this should make a noticeable difference in the time the query runs.

Unless every column has an index, than it might be faster to go with subqueries for each column using a WHERE clause to filter for the NULLs. That could look like:

SELECT (SELECT count(*)
               FROM elbat
               WHERE nmuloc1 IS NULL) nmuloc1_null_count,
       ...
       (SELECT count(*)
               FROM elbat
               WHERE nmuloc100 IS NULL) nmuloc100_null_count;

And then I wouldn't use a function for the subqueries either as that tends to result in an additional significant decrease in speed. To make it easier you could generate (one of) the queries by a little program in any language you like, that you feed the metadata (column names) and repeats the expression for each column.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You have multiple problems, too long for a comment.

  1. You are assigning a string to an integer. That is the proximal cause of your error.
  2. Assigning a string and returning the value is not going to do anything useful.
  3. You intend to use dynamic SQL, but SQL Server does not allow dynamic SQL in a column.

I'm not sure what value dynamic SQL has over simply writing the code:

select count(*)
from tablename
where <col> is null;

Perhaps you are coming from a programming background and think that writing functions is a good way to encapsulate logic. That paradigm does not work as well with databases.

Upvotes: 1

Related Questions