Validate decimal and integer values by looping through list of dynamic columns

user_1025057 0
Viewed 53

I was tasked to validate the decimal and integer values of the columns from a list of tables. I have around 10-12 tables having different column names.
I created a lookup table which has the table name and the column names of decimal and integer as shown below. for example 'Pricedetails' and 'Itemdetails' tables have many columns of which only the ones mentioned in the Lookup table are required.

lkpTable

TableName requiredcolumns
Pricedetails sellingPrice,RetailPrice,Wholesaleprice
Itemdetails ItemID,Itemprice

Pricedetails

Priceid Mafdate MafName sellingPrice RetailPrice Wholesaleprice
01 2020-01-01 Americas 25.00 43.33 33.66
02 2020-01-01 Americas 43.45 22.55 11.11
03 2021-01-01 Asia -23.00 -34.00 23.00

Itemdetails

ItemID ItemPrice Itemlocation ItemManuf
01 45.11 Americas SA
02 25.00 Americas SA
03 35.67 Americas SA

I have created a stored procedure with table name as input parameter, and able to pull the required column names of the tables (input parameter) from the lookup table and store that resultset into a table variable, below is the code.

declare @resultset Table
(
id INT identity(1,1),
tablename varchar(200) ,
ColumnNames varchar(max)
)

declare @tblname varchar(200),@sql varchar(max),@cols varchar(max),
INSERT INTO @resultset
select tablename,ColumnNames
from lkptable where tablename ='itemdetails'
select @cols =  ColumnNames from @resultset;
select @tblname = TableName from @resultset;
----- Split the comma separated columnnames 
Create table ##splitcols
(
ID int identity(1,1),
Name varchar(50)
)
Insert into ##splitcols
select value from string_split(@cols,',') 
set @sql = 'select ' +@cols + ' from ' +@tblname
--print (@cols)
exec (@sql)
select * from ##splitcols

On executing the above code i get the below result sets, similarly what ever table name i provide i can get the required columns and its relevant data, now i am stuck at this point on how to validate whether the columns are decimal or int. I tried using while loop and cursor to pass the Name value from Resultset2, to the new dynamic query, somehow i don't find any way on how to validate.

ItemID ItemPrice
01 45.11
02 25.00
03 35.67

Resultset2

ID Name
01 ItemID
02 ItemPrice
dynamic-sql tsql sql-server
2 Answers

you can validate in this way

insert into @splitcols values (1.1),(1.11)

select case when (t * 100)%10 = 0 then 1 else 0 end as valid from @splitcols

Similarly for number/integer

user_75125190

You can generate one giant UNION ALL query using dynamic SQL, then run it.

Each query would be of the form:

SELECT
  TableName = 'SomeTable',
  ColumnName,
  IsInt
FROM (
    SELECT 
    [Column1] = CASE WHEN COUNT(CASE WHEN ROUND([Column1], 0) <> [Column1] THEN 1 END) = 0 THEN 'All ints' ELSE 'Not All ints' END,
    [Column2] = CASE WHEN COUNT(CASE WHEN ROUND([Column2], 0) <> [Column2] THEN 1 END) = 0 THEN 'All ints' ELSE 'Not All ints' END
    FROM SomeTable
) t
UNPIVOT (
  ColumnName FOR IsInt IN (
    [Column1], [Column2]
  )
) u

The script is as follows

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG('
SELECT
  TableName = ' + QUOTENAME(t.name, '''') + ',
  ColumnName,
  IsInt
FROM (
    SELECT ' + c.BeforePivotCoumns + '
    FROM ' + QUOTENAME(t.name) + '
) t
UNPIVOT (
  ColumnName FOR IsInt IN (
    ' + c.UnpivotColumns + '
  )
) u
', '
UNION ALL '
  )
FROM sys.tables t
JOIN lkpTable lkp ON lkp.TableName = t.name
CROSS APPLY (
    SELECT
      BeforePivotCoumns = STRING_AGG(CAST('
    ' + QUOTENAME(c.name) + ' = CASE WHEN COUNT(CASE WHEN ROUND(' + QUOTENAME(c.name) + ', 0) <> ' + QUOTENAME(c.name) + ' THEN 1 END) = 0 THEN ''All ints'' ELSE ''Not All ints'' END'
        AS nvarchar(max)), ','),
      
      UnpivotColumns = STRING_AGG(QUOTENAME(c.name), ', ')
      
    FROM sys.columns c
    JOIN STRING_SPLIT(lkp.requiredcolumns, ',') req ON req.value = c.name
    WHERE c.object_id = t.object_id
) c;

PRINT @sql;

EXEC sp_executesql @sql;

db<>fiddle

If you are on an older version of SQL Server then you can't use STRING_AGG and instead you need to hack it with FOR XML and STUFF.

DECLARE @unionall nvarchar(100) = '
UNION ALL ';

DECLARE @sql nvarchar(max);

SET @sql = STUFF(
  (SELECT @unionall + '
SELECT
  TableName = ' + QUOTENAME(t.name, '''') + ',
  ColumnName,
  IsInt
FROM (
    SELECT ' + STUFF(c1.BeforePivotCoumns.value('text()[1]','nvarchar(max)'), 1, 1, '') + '
    FROM ' + QUOTENAME(t.name) + '
) t
UNPIVOT (
  ColumnName FOR IsInt IN (
    ' + STUFF(c2.UnpivotColumns.value('text()[1]','nvarchar(max)'), 1, 1, '') + '
  )
) u'
FROM sys.tables t
JOIN (
    SELECT DISTINCT
      lkp.TableName
    FROM lkpTable lkp
) lkp ON lkp.TableName = t.name

CROSS APPLY (
    SELECT
      ',
    ' + QUOTENAME(c.name) + ' = CASE WHEN COUNT(CASE WHEN ROUND(' + QUOTENAME(c.name) + ', 0) <> ' + QUOTENAME(c.name) + ' THEN 1 END) = 0 THEN ''All ints'' ELSE ''Not All ints'' END'
    FROM lkpTable lkp2
    CROSS APPLY STRING_SPLIT(lkp2.requiredcolumns, ',') req
    JOIN sys.columns c ON req.value = c.name
    WHERE c.object_id = t.object_id
      AND lkp2.TableName = lkp.TableName
    FOR XML PATH(''), TYPE
) c1(BeforePivotCoumns)

CROSS APPLY (
    SELECT
       ', ' + QUOTENAME(c.name)
    FROM lkpTable lkp2
    CROSS APPLY STRING_SPLIT(lkp2.requiredcolumns, ',') req
    JOIN sys.columns c ON req.value = c.name
    WHERE c.object_id = t.object_id
      AND lkp2.TableName = lkp.TableName
    FOR XML PATH(''), TYPE
) c2(UnpivotColumns)

FOR XML PATH(''), TYPE
).value('text()[1]','nvarchar(max)'), 1, LEN(@unionall), '');


PRINT @sql;

EXEC sp_executesql @sql;

db<>fiddle

user_148689970
Related