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

SQL9 246 Reputation points
2020-12-18T20:41:52.9+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
{count} votes

7 answers

Sort by: Most helpful
  1. Nasreen Akter 10,761 Reputation points
    2020-12-18T21:21:55.847+00:00

    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  
    
    0 comments No comments

  2. SQL9 246 Reputation points
    2020-12-18T22:06:59.06+00:00

    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

    0 comments No comments

  3. Guoxiong 8,126 Reputation points
    2020-12-18T22:47:49.627+00:00

    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;
    
    0 comments No comments

  4. Erland Sommarskog 102.3K Reputation points
    2020-12-18T22:51:47.84+00:00

    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
    
    0 comments No comments

  5. EchoLiu-MSFT 14,571 Reputation points
    2020-12-21T09:04:52.3+00:00

    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