Stored Procedure for dynamically comparing the count between the source schema tables and target schema tables in Snowflake

I have written a stored procedure that will dynamically compare row counts between source and target tables in two different schemas and databases and iterate over all tables in a schema, compare row counts, and log any discrepancies for review.

But when i tried to execute the procedure, it throws an error as ❌ Procedure failed: source_database is not defined

 `CREATE OR REPLACE PROCEDURE TEST1_validate_all_table_row_counts(
    source_database STRING,
    source_schema STRING,
    target_database STRING,
    target_schema STRING
)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
try {
    var result_log = [];

    // Convert input parameters to uppercase for consistency
    var src_db = source_database.toUpperCase();
    var src_schema = source_schema.toUpperCase();
    var tgt_db = target_database.toUpperCase();
    var tgt_schema = target_schema.toUpperCase();

    result_log.push(`🔹 Validating row counts between ${src_db}.${src_schema} and ${tgt_db}.${tgt_schema}`);

    // Get all tables from the source schema
    var sql_get_tables = `
        SELECT TABLE_NAME 
        FROM "` + src_db + `"."INFORMATION_SCHEMA"."TABLES"
        WHERE UPPER(TABLE_SCHEMA) = '` + src_schema + `'
        AND TABLE_TYPE = 'BASE TABLE'
        ORDER BY TABLE_NAME`;

    var stmt = snowflake.execute({ sqlText: sql_get_tables });

    var table_count = 0;
    while (stmt.next()) {
        table_count++;
        var table_name = stmt.getColumnValue(1);
        result_log.push(`🔹 Checking table: ${table_name}`);

        // Check if table exists in target schema
        var sql_check_target = `
            SELECT COUNT(*) 
            FROM "` + tgt_db + `"."INFORMATION_SCHEMA"."TABLES"
            WHERE UPPER(TABLE_SCHEMA) = '` + tgt_schema + `'
            AND UPPER(TABLE_NAME) = '` + table_name.toUpperCase() + `'`;

        var check_stmt = snowflake.execute({ sqlText: sql_check_target });
        check_stmt.next();
        var table_exists = check_stmt.getColumnValue(1);

        if (table_exists === 0) {
            result_log.push(`⚠️ Skipped: ${table_name} does not exist in target schema.`);
            continue;
        }

        // Row count queries
        var sql_source = `SELECT COUNT(*) FROM "` + src_db + `"."` + src_schema + `"."` + table_name + `"`;
        var sql_target = `SELECT COUNT(*) FROM "` + tgt_db + `"."` + tgt_schema + `"."` + table_name + `"`;

        result_log.push(`Executing SQL on Source: ${sql_source}`);
        result_log.push(`Executing SQL on Target: ${sql_target}`);

        try {
            var source_stmt = snowflake.execute({ sqlText: sql_source });
            var target_stmt = snowflake.execute({ sqlText: sql_target });

            source_stmt.next();
            target_stmt.next();

            var source_count = source_stmt.getColumnValue(1);
            var target_count = target_stmt.getColumnValue(1);

            if (source_count === target_count) {
                result_log.push(`✅ Match: ${table_name} -> Source: ${source_count}, Target: ${target_count}`);
            } else {
                result_log.push(`❌ Mismatch: ${table_name} -> Source: ${source_count}, Target: ${target_count}`);
            }
        } catch (err) {
            result_log.push(`⚠️ Error processing table ${table_name}: ${err.message}`);
        }
    }

    // If no tables were found, return a message
    if (table_count === 0) {
        result_log.push(`⚠️ No tables found in source schema: ${src_schema}`);
    }

    return result_log.join("\n");

} catch (err) {
    return `❌ Procedure failed: ${err.message}`;
}
$$;

`

You should write your parameters in UPPERCASE:

    var src_db = SOURCE_DATABASE.toUpperCase();
    var src_schema = SOURCE_SCHEMA.toUpperCase();
    var tgt_db = TARGET_DATABASE.toUpperCase();
    var tgt_schema = TARGET_SCHEMA.toUpperCase();