How can i set a variable from table column in dynamic query ?

Question

Hi all, I want to write a query which include database tables' column null counts. I wrote a code like below but it does not read @nullcnt parameter. What is the syntax error ?

BEGIN
declare @schemaName varchar(100);
declare @tableName varchar(100);
declare @columnName varchar(100);
declare @nullcnt   varchar(100);
declare @ddFlag   varchar(100);
declare @sql nvarchar(max); 

	DECLARE @i int =1 ;
	DECLARE @z int ;
	select @z=count(1) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA in ('edw','dm');

	WHILE (@i<=@z)
	BEGIN
	--get the  @schemaName,@tableName ,@ddFlag vrb

	select @schemaName=TABLE_SCHEMA,@tableName=TABLE_NAME,@ddFlag=dd from (
	select 
	ROW_NUMBER() over(order by TABLE_SCHEMA) as rw,TABLE_SCHEMA,TABLE_NAME,dd
	from
	( select TABLE_SCHEMA, TABLE_NAME ,sum(case when COLUMN_NAME='data_date' then 1 else 0 end)  as dd from INFORMATION_SCHEMA.COLUMNS 
	where TABLE_SCHEMA in ('edw','dm')
	group by TABLE_SCHEMA,TABLE_NAME
	) a
	) b where rw=@i

	--get the column names
	DECLARE @t1 int =1 ;
	DECLARE @t int ;
	select @t=count(1) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tableName and TABLE_SCHEMA=@schemaName;

	WHILE (@t1<=@t)
	BEGIN
	
	select @columnName=COLUMN_NAME  from (
	select ROW_NUMBER() over(order by TABLE_SCHEMA) as rw,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tableName and TABLE_SCHEMA=@schemaName
	) a where rw=@t1
	 
	set @sql='select @nullcnt=count(1)  from ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) + ' where ' + QUOTENAME(@columnName) + ' is null '
	print @nullcnt
	
	SET @t1=@t1+1
	END
	SET @i=@i+1
	END


END

Answer 1

Hi @Mustafa Ispir

What is the syntax error ?

Before you print @nullcnt , you need to assign a value to this variable first, while you didn't.

Try this:

set @sql='select @nullcnt=count(1)  from ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) + ' where ' + QUOTENAME(@columnName) + ' is null '
EXECUTE [sys].[sp_executesql] @sql,N'@nullcnt int OUTPUT',@nullcnt = @nullcnt OUTPUT;
SELECT TABLE_SCHEMA=@schemaName
      ,TABLE_NAME=@tableName
	  ,COLUMN_NAME=@columnName
	  ,nullcnt=@nullcnt
SET @t1=@t1+1
END
SET @i=@i+1
END

Also, you could try this query using STRING_AGG which is supported by SQL Server 2017 (14.x) and later.

DECLARE @sql nvarchar(max) =
(
SELECT STRING_AGG(TableQuery, N'
UNION ALL
')
FROM (
SELECT N'
SELECT
  TableName,
  Col as ColumnName,
  TotalRows,
  NonNullRows,
  NullsRows = TotalRows - NonNullRows
FROM (SELECT
      TableName = ' + QUOTENAME(t.name, '''') + N',
      TotalRows = COUNT(*),
      ' + STRING_AGG(CAST(QUOTENAME(c.name) + ' = COUNT(' + QUOTENAME(c.name) + ')' AS nvarchar(max)), ',') + N' FROM ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + N')t
UNPIVOT (NonNullRows FOR Col IN (
    ' + STRING_AGG(CAST(QUOTENAME(c.name) AS nvarchar(max)), ',') + N'
)) p'
FROM sys.tables t JOIN sys.columns c ON c.object_id = t.object_id
-- WHERE filter
GROUP BY t.object_id, t.name, t.schema_id
) AS t(TableQuery));

--PRINT @sql;  -- for testing

EXEC sp_executesql @sql;

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

Answer 2

Try this:

EXECUTE [sys].[sp_executesql]
	@stmt = @sql,
	@params = N'@nullcnt int OUTPUT',
	@nullcnt = @nullcnt OUTPUT;
PRINT @nullcnt

Answer 3

I was trying to find my own answer on this topic about 10 years ago, but found this instead which should be OK (the answer with 12 votes) https://stackoverflow.com/questions/16528682/count-null-values-from-multiple-columns-with-sql