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

Question

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

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

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

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