create or replace procedure RUN_DYNAMIC_SQL("sqlStatement" string)
returns variant
language javascript
execute as caller
as
$$

class Query{
    constructor(statement){
        this.statement = statement;
    }
}

var out = {};
var query = getQuery(sqlStatement);
if (query.error == null) {
    return rsToJSON(query);
} else {
    return {"error": query.error};
}

function rsToJSON(query) {
    var i;
    var row = {};
    var table = [];
    while (query.resultSet.next()) {
        for(col = 1; col <= query.statement.getColumnCount(); col++) {
            row[query.statement.getColumnName(col)] = query.resultSet.getColumnValue(col);
        }
        table.push(row);
    }
    return table;
}

function getQuery(sql){
    var cmd = {sqlText: sql};
    var query = new Query(snowflake.createStatement(cmd));
    try {
        query.resultSet = query.statement.execute();
    } catch (e) {
        query.error = e.message;
    }
    return query;
}
$$;

-- Usage samples:

-- Create a table. Note the use of $$ to define strings to avoid problems with single quotes
call run_dynamic_sql($$ create or replace temp table foo(v variant) $$);

-- Run a select statement. While this SP is intended to execute non queries, it will also
-- Return a query's result set as a JSON as long as the JSON is under 16 MB in size.
call run_dynamic_sql($$ select * from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."NATION" $$);

-- Show an example copying into a dynamically-named path in a stage:
-- Create a scratch stage for the test
create or replace stage my_stage;

-- Set a variable for the path
set today = current_date;

select $today;

-- Build the copy command. Use ' or $$ to terminate strings as convenient.
set copycommand = 'copy into @my_stage/' || $TODAY || '/data.csv' ||
$$ from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."NATION" file_format = (type = CSV, field_optionally_enclosed_by = '"') $$;

-- Examine the statement to make sure it looks okay
select $copycommand;

-- Copy the file to the dynamic path
call run_dynamic_sql($copycommand);

create stage mystage;
call run_dynamic_sql('copy into @mystage/' || current_date || '/data.csv from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION file_format = (type = CSV)');