SQLize Online / PHPize Online  /  SQLtest Online

CREATE OR REPLACE PROCEDURE `sincere-apex-416007.cap_project.compare_tables` ( table1_name STRING, table2_name STRING, columns_to_exclude ARRAY<STRING> ) OPTIONS (strict_mode = false) BEGIN DECLARE table1_checksum STRING DEFAULT ''; DECLARE table2_checksum STRING DEFAULT ''; DECLARE columns_list STRING DEFAULT ''; DECLARE columns_to_compare ARRAY<STRING> DEFAULT []; DECLARE failure_reason_query STRING DEFAULT ''; DECLARE columns_filtered ARRAY<STRING> DEFAULT []; CREATE TEMP FUNCTION ArrayToString (arr ARRAY<STRING>) RETURNS STRING LANGUAGE js AS """ return arr.join(', '); """; CREATE TEMP FUNCTION GenerateChecksumExpression (columns ARRAY<STRING>) RETURNS STRING LANGUAGE js AS """ return columns.map(col => `MD5(IFNULL(CAST(${col} AS STRING), " NULL "))`).join(' || '); """; SET columns_to_compare = ( SELECT ARRAY_AGG(column_name) FROM ( SELECT column_name FROM `sincere-apex-416007.cap_project.INFORMATION_SCHEMA.COLUMNS` WHERE table_name = table1_name UNION DISTINCT SELECT column_name FROM `sincere-apex-416007.cap_project.INFORMATION_SCHEMA.COLUMNS` WHERE table_name = table2_name ) ); SET columns_to_compare = ARRAY( SELECT col FROM UNNEST(columns_to_compare) AS col WHERE NOT col IN UNNEST(columns_to_exclude) ); IF ARRAY_LENGTH(columns_to_compare) > 0 THEN SET columns_filtered = ARRAY( SELECT col FROM UNNEST(columns_to_compare) AS col ); SET table1_checksum = GenerateChecksumExpression(columns_filtered); SET table2_checksum = GenerateChecksumExpression(columns_filtered); SET columns_list = ArrayToString(columns_filtered); ELSE SET table1_checksum = '1'; -- Dummy value to avoid null SET table2_checksum = '1'; -- Dummy value to avoid null SET columns_list = ''; -- Empty string for columns list END IF; IF ARRAY_LENGTH(columns_filtered) > 0 THEN SET failure_reason_query = ( SELECT STRING_AGG( 'SELECT CASE WHEN CAST(t1.' || col || ' AS STRING) != CAST(t2.' || col || ' AS STRING) THEN "Mismatch in ' || col || '" ELSE NULL END AS Reason', ' UNION ALL ' ) FROM UNNEST(columns_filtered) AS col ); ELSE SET failure_reason_query = 'SELECT NULL AS Reason'; END IF; -- Debugging outputs SELECT table1_checksum AS debug_table1_checksum, table2_checksum AS debug_table2_checksum, columns_list AS debug_columns_list, failure_reason_query AS debug_failure_reason_query; IF table1_checksum IS NOT NULL AND table2_checksum IS NOT NULL AND columns_list IS NOT NULL THEN EXECUTE IMMEDIATE """ WITH Table1 AS ( SELECT BusinessDate, LoadDate""" || (CASE WHEN columns_list != '' THEN ', ' || columns_list ELSE '' END) || """, """ || table1_checksum || """ AS Table1_Checksum FROM """ || table1_name || """ ), Table2 AS ( SELECT BusinessDate, LoadDate""" || (CASE WHEN columns_list != '' THEN ', ' || columns_list ELSE '' END) || """, """ || table2_checksum || """ AS Table2_Checksum FROM """ || table2_name || """ ), Comparison AS ( SELECT '""" || table1_name || """' AS Table1Name, '""" || table2_name || """' AS Table2Name, t1.BusinessDate, t1.LoadDate, CASE WHEN t1.Table1_Checksum = t2.Table2_Checksum THEN 'Pass' ELSE 'Fail' END AS ResultPass, CASE WHEN t1.Table1_Checksum != t2.Table2_Checksum THEN ( SELECT STRING_AGG(Reason, '; ') FROM (""" || failure_reason_query || """) WHERE Reason IS NOT NULL ) ELSE NULL END AS FailureReason FROM Table1 t1 JOIN Table2 t2 ON t1.BusinessDate = t2.BusinessDate ) SELECT Table1Name, Table2Name, BusinessDate, LoadDate, ResultPass, FailureReason FROM Comparison ORDER BY BusinessDate; """; ELSE -- Handle case where checksums or columns list is null RAISE USING MESSAGE = 'Checksum expressions or columns list cannot be NULL'; END IF; END;