Untitled

CREATE OR REPLACE PROCEDURE SP_CHECK_APPL_IDS(APPL_IDS STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var db = 'WDRUAT_CONSUMPTION_DB';
var schema = 'WDR_CL_CORE';

// Step 1: Create temp table
snowflake.createStatement({
        sqlText: `CREATE OR REPLACE TEMP TABLE TMP_RESULT (
                            TABLE_NAME STRING,
                                            PRESENT STRING
        )`
}).execute();

// Step 2: Format input
var appl_ids_formatted = APPL_IDS
    .split(',')
        .map(x => "'" + x.trim() + "'")
            .join(',');

            // Step 3: Get tables
            var sql_tables = `
            SELECT TABLE_NAME,
                   MAX(CASE WHEN COLUMN_NAME = 'CURRENT_IND' THEN 1 ELSE 0 END) AS HAS_CURRENT_IND
                   FROM ` + db + `.INFORMATION_SCHEMA.COLUMNS
                   WHERE TABLE_SCHEMA = '` + schema + `'
                     AND COLUMN_NAME IN ('APPL_KEY','CURRENT_IND')
                     GROUP BY TABLE_NAME
                     HAVING MAX(CASE WHEN COLUMN_NAME = 'APPL_KEY' THEN 1 ELSE 0 END) = 1
                     `;

                     var rs = snowflake.createStatement({sqlText: sql_tables}).execute();

                     // Step 4: Loop + insert results
                     while (rs.next()) {
                            var table = rs.getColumnValue(1);
                                var has_curr = rs.getColumnValue(2);

                                    var filter = "APPL_KEY IN (" + appl_ids_formatted + ")";
                                        if (has_curr == 1) {
                                                    filter += " AND CURRENT_IND = 'Y'";
                                        }

                                            var insert_sql = `
                                                    INSERT INTO TMP_RESULT
                                                            SELECT '${table}',
                                                                           CASE WHEN EXISTS (
                                                                                               SELECT 1
                                                                                                                  FROM ` + db + `.` + schema + `.` + table + `
                                                                                                                                     WHERE ` + filter + `
                                                                                                                                                        LIMIT 1
                                                                           )
                                                                                          THEN 'YES' ELSE 'NO' END
                                                                                              `;

                                                                                                  snowflake.createStatement({sqlText: insert_sql}).execute();
                     }

                     return 'DONE';
                     $$;
                                                                           )
                                        }
                     }
        )
})