Dynamic SQL table validation for data quality dimension

Question

I have the following code to test for nulls in a whole table using dynamic sql:

/*Completitud*/
--Housekeeping:
drop table if exists tmp_completitud;
--Declarar variables para el loop:
declare @custom_sql   VARCHAR(max)
declare @tablename as VARCHAR(255) = 'maestrodatoscriticos' --Nombre de tabla a usar.
--Reemplazar '_[dimension]' como "apellido" de la tabla por cada nueva dimension:
set @custom_sql = 'select ''' + @tablename + '_Completitud' + ''' as tabla'
select @custom_sql =
           --Reemplazar query de dimension aqui:
       @custom_sql + ', ' + 'sum(cast(iif(' + c.name + ' is null,0,1) as decimal)) / count(*) as ' + c.name
from sys.columns c
         inner join sys.tables t on c.object_id = t.object_id
where t.name = @tablename
set @custom_sql = @custom_sql + ' into tmp_completitud from ' + @tablename
--print @custom_sql
exec (@custom_sql);
--Poblar tabla de dimensiones con dimension actual:
insert into dimensiones
select *
from tmp_completitud;

I now want to test for unique values, but I'm having a hard time with aggregate functions inside a subquery. So far I have:

select sum(cast(iif(
            ( select sum(cnt) from ( select count(distinct identificacion) as cnt from maestrodatoscriticos ) as x ) =
            ( select sum(cnt2) from ( select count(identificacion) as cnt2 from maestrodatoscriticos ) as y ), 0,
            1) as decimal)) / count(*)
from maestrodatoscriticos;

And I would like to somehow integrate the select sum(cast(iif... into the select @custom_sql = ... above. Any ideas?

score 0 · Answer 1 · answered Nov 08 '21 at 20:29

I actually resolved this with some help from a co-worker. The code is:

/*Unicidad*/
--Housekeeping:
drop table if exists tmp_unicidad;
--Declarar variables para el loop:
declare @sqluni VARCHAR(max) declare @tableuni as VARCHAR(255) = 'maestrodatoscriticos' --Nombre de tabla a usar.
--Reemplazar '_[dimension]' como "apellido" de la tabla por cada nueva dimension:
set @sqluni = 'select ''' + @tableuni + '_Unicidad' + ''' as tabla'
select @sqluni =
           --Reemplazar query de dimension aqui:
       @sqluni + ', ' + 'count(distinct ' + c.name + ') * 1.00 / count(*) * 1.00 as ' + c.name
from sys.columns c
         inner join sys.tables t on c.object_id = t.object_id
where t.name = @tableuni
set @sqluni = @sqluni + ' into tmp_unicidad from ' + @tableuni
--print @custom_sql
exec (@sqluni);
--Poblar tabla de dimensiones con dimension actual:
insert into dimensiones
select *
from tmp_unicidad;

The key was in not using `sum` or `cast` but just straight math from the `select`: ```select count(distinct column) * 1.00 / count(*) * 1.00 as alias``` — metarodri, Nov 08 '21 at 20:31

Dynamic SQL table validation for data quality dimension

1 Answers1