Need t-sql code to count number of rows which are not null in a column in a sql table

Question

Need t-sql code to count number of rows which are not null in a column in a sql table

SQL9 246

Hi All,

I have a source table with 3 columns(ID, TableName , SQLText) with below sample data

ID TableName SQLText
1 [dbo].Customer Select count(customeraddress1) from [dbo].Customer where customeraddress1 is not null
2 [dbo].Customer Select count(customeraddress2) from [dbo].Customer where customeraddress2 is not null
3 [dbo].Customer Select count(customeraddress3) from [dbo].Customer where customeraddress3 is not null
4 [dbo].Product Select count(ProductDescription) from [dbo].Product where ProductDescription is not null
5 [dbo].Product Select count(Discount) from [dbo].Product where Discount is not null

I need T-SQL code to get counts when SQLText column is executed using dynamic SQL.
The value returned after executing SQLText column need to be stored in SQLStmtCount column.

My output should look like below

ID TableName SQLStmtCount SQLText
1 [dbo].Customer 100 Select count(customeraddress1) from [dbo].Customer where customeraddress1 is not null
2 [dbo].Customer 75 Select count(customeraddress2) from [dbo].Customer where customeraddress2 is not null
3 [dbo].Customer 0 Select count(customeraddress3) from [dbo].Customer where customeraddress3 is not null
4 [dbo].Product 50 Select count(ProductDescription) from [dbo].Product where ProductDescription is not null
5 [dbo].Product 10 Select count(Discount) from [dbo].Product where Discount is not null

create table SQLData
(
ID int null , TableName varchar(100) null , SQLText Varchar(max) null
)

insert into SQLData (ID , TableName , SQLText)
Values
(1 , '[dbo].Customer' , 'Select count(customeraddress1) from [dbo].Customer where customeraddress1 is not null'),
(2 , '[dbo].Customer' , 'Select count(customeraddress2) from [dbo].Customer where customeraddress2 is not null'),
(3 , '[dbo].Customer' , 'Select count(customeraddress3) from [dbo].Customer where customeraddress3 is not null'),
(4 , '[dbo].Product' , 'Select count(ProductDescription) from [dbo].Product where ProductDescription is not null'),
(5 , '[dbo].Product' , 'Select count(Discount) from [dbo].Product where Discount is not null)'

Thanks in advance,
RH

Viorel 89,136

You can also consider a single long dynamic query like this:

declare @sql varchar(max) = concat( 'select t.ID, d.TableName, t.SQLStmtCount, d.SQLText from (values ',
(select string_agg(concat('(', ID, ', (', SQLText, '))'), ', ') from SQLData), ') t(ID, SQLStmtCount) inner join SQLData d on d.ID=t.ID')

exec (@sql)

(By the way, you can remove all of ‘where … is not null’ in these particular cases).

Erland Sommarskog 72,426 Reputation points MVP

2020-12-19T10:48:14.307+00:00

I purposefully avoided producing a single statement in my solution.

We don't know how many rows there are the actual table, but the VALUES constructor has a limit of 1000 rows. This can be avoided by using SELECT with UNION ALL instead. However, there is a high cost for compiling statements with many UNION operators, and this is visible already with less than 1000 of them. (The limitation with VALUES exists because exactly of this reason. VALUES is only syntactic sugar on SELECT UNION ALL.)

For that reason, my solution produces one SQL statement per row, and then I collect all rows with INSERT-EXEC.
EchoLiu-MSFT 14,501 Reputation points

2020-12-25T09:09:20.04+00:00

any updates?

Echo

Viorel 89,136 Reputation points

2020-12-19T10:21:14.987+00:00

You can also consider a single long dynamic query like this:

declare @sql varchar(max) = concat( 'select t.ID, d.TableName, t.SQLStmtCount, d.SQLText from (values ', (select string_agg(concat('(', ID, ', (', SQLText, '))'), ', ') from SQLData), ') t(ID, SQLStmtCount) inner join SQLData d on d.ID=t.ID') exec (@sql)

(By the way, you can remove all of ‘where … is not null’ in these particular cases).
Erland Sommarskog 72,426 Reputation points MVP

2020-12-19T10:48:14.307+00:00

I purposefully avoided producing a single statement in my solution.

We don't know how many rows there are the actual table, but the VALUES constructor has a limit of 1000 rows. This can be avoided by using SELECT with UNION ALL instead. However, there is a high cost for compiling statements with many UNION operators, and this is visible already with less than 1000 of them. (The limitation with VALUES exists because exactly of this reason. VALUES is only syntactic sugar on SELECT UNION ALL.)

For that reason, my solution produces one SQL statement per row, and then I collect all rows with INSERT-EXEC.
EchoLiu-MSFT 14,501 Reputation points

2020-12-25T09:09:20.04+00:00

any updates?

Echo

Answer 1

Hi @SQL9 ,

Thank you for asking this question. Try the following statements.

Hope this helps. If it does, please don't forget to "accept the answer" and "up-vote"! Thank you!

create table SQLData  
(  
ID int null , TableName varchar(100) null , SQLText Varchar(max) null  
)  
  
insert into SQLData (ID , TableName , SQLText)  
Values  
(1 , '[dbo].Customer' , concat((Select count(customeraddress1) from [dbo].Customer where customeraddress1 is not null), ' Select count(customeraddress1) from [dbo].Customer where customeraddress1 is not null')),  
(2 , '[dbo].Customer' , concat((Select count(customeraddress2) from [dbo].Customer where customeraddress2 is not null), ' Select count(customeraddress2) from [dbo].Customer where customeraddress2 is not null')),  
(3 , '[dbo].Customer' , concat((Select count(customeraddress3) from [dbo].Customer where customeraddress3 is not null), ' Select count(customeraddress3) from [dbo].Customer where customeraddress3 is not null')),  
(4 , '[dbo].Product' , concat((Select count(ProductDescription) from [dbo].Product where ProductDescription is not null), ' Select count(ProductDescription) from [dbo].Product where ProductDescription is not null')),  
(5 , '[dbo].Product' , concat((Select count(Discount) from [dbo].Product where Discount is not null), ' Select count(Discount) from [dbo].Product where Discount is not null'))  
  
Select * from SQLData

Answer 2

SQL9 246

Hi Nasreen - Thanks for the reply but I think concat will not give the counts that I need from SQLText column.
We have to execute SQLText column values using dynamic SQL(EXECUTE sp_ExecuteSQL) statement but not sure how to use it properly.

Thanks,
RH

Answer 3

Try this:

DECLARE @Count int;
DECLARE @SqlStmt nvarchar(max)
DECLARE @ID int;
DECLARE @TableName varchar(100);
DECLARE @SQLText varchar(max);
DECLARE @Output TABLE (
    ID int,
    TableName varchar(100),
    SQLStmtCount int,
    SQLText varchar(max)
);

DECLARE ID_Cursor CURSOR FOR SELECT ID, TableName, SQLText FROM SQLData;
OPEN ID_Cursor;
FETCH NEXT FROM ID_Cursor INTO @ID, @TableName, @SQLText;

WHILE @@FETCH_STATUS = 0  
BEGIN
    SET @SqlStmt = REPLACE(@SQLText, 'SELECT ', 'SELECT @Count = ')
    EXECUTE sp_executesql @SqlStmt, N'@count int OUTPUT', @Count = @Count OUTPUT;

    INSERT INTO @Output(ID, TableName, SQLStmtCount, SQLText)
    VALUES(@ID, @TableName, @Count, @SQLText);

    FETCH NEXT FROM ID_Cursor INTO @ID, @TableName, @SQLText;
END
CLOSE ID_Cursor;
DEALLOCATE ID_Cursor;

SELECT * FROM @Output;

Answer 4

Try this. I don't have the tables in your SQLText columns, so I could not test the whole through, but it looks good to me.

You did not say which version of SQL Server you working on. This solution only runs on SQL 2017 and later.

create table SQLData
(
ID int null , TableName varchar(100) null , SQLText Varchar(max) null
)

insert into SQLData (ID , TableName , SQLText)
Values
(1 , '[dbo].Customer' , 'Select count(customeraddress1) from [dbo].Customer where customeraddress1 is not null'),
(2 , '[dbo].Customer' , 'Select count(customeraddress2) from [dbo].Customer where customeraddress2 is not null'),
(3 , '[dbo].Customer' , 'Select count(customeraddress3) from [dbo].Customer where customeraddress3 is not null'),
(4 , '[dbo].Product' , 'Select count(ProductDescription) from [dbo].Product where ProductDescription is not null'),
(5 , '[dbo].Product' , 'Select count(Discount) from [dbo].Product where Discount is not null')

DECLARE @sql nvarchar(MAX)

CREATE TABLE #result(ID           int          NOT NULL PRIMARY KEY,
                     TableName    varchar(100) NULL,
                     SQLStmtCount int          NULL,
                     SQLText      varchar(MAX) NOT NULL)

SELECT @sql = string_agg('SELECT ' + convert(varchar(MAX), ID) + ', (' + SQLText + ')', char(13) + char(10))
FROM   SQLData

PRINT @sql

INSERT #result(ID, SQLStmtCount)
  EXEC(@sql)

UPDATE #result
SET    TableName = s.TableName,
       SQLText   = s.SQLText
FROM   #result r
JOIN   SQLData s ON r.ID = s.ID

Answer 5

EchoLiu-MSFT 14,501

Hi @SQL9 ,

When you post a question, please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result. So that we’ll get a right direction and make some test.

I did a partial test and ErlandSommarskog's method seems to work.
Add a sentence，temporary tables do not need to be created in advance:

     select * into #result  
     from sourcetable

Regards
Echo

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

SQL9 246 Reputation points

2020-12-21T14:49:31.38+00:00

Hi EchoLiu-msft,

FYI - In my original post I have provided create and insert statements with sample data.

Thanks,
RH
EchoLiu-MSFT 14,501 Reputation points

2020-12-22T09:41:25.113+00:00

Yes, but if there are [dbo].Customer and Product data, it will be more perfect.You also will get answers faster.

Regards
Echo

Need t-sql code to count number of rows which are not null in a column in a sql table

7 answers

Activity