Trying to pass parameter as binding variable in snowflake statement

user_13973027 0
Viewed 7115

Below is my stored procedure, I'm not sure as to why it keeps throwing an error. The error I get is

SQL compilation error: syntax error line XX at position XX unexpected '?'.

I have followed the documentation here but it does not seem to work for me.

This is what I have:

CREATE OR REPLACE PROCEDURE spExample(INPUT_TABLE VARCHAR)
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    AS
    $$
    result = "";
    try {
        var sql_cmd = "SELECT * FROM ?;";
        var sql_stmt = snowflake.createStatement({sqlText: sql_cmd, binds:[INPUT_TABLE]});
        sql_stmt.execute();
    } catch(err) {
        result += "Message: " + err.message;
    }
    return result;
    $$;

Have I made a mistake somewhere?

snowflake-cloud-data-platform bind-variables stored-procedures
4 Answers

Above answers subject your code to sql injection attack. And of course you can bind a table name to a variable in snowflake.

Do
var sql_cmd = "SELECT * FROM IDENTIFIER('?');";

user_25555150

It seems I had the exact same misunderstanding as the OP. It was good to find this answer.

In any case, it's a lot more flexible & more readable to use JavaScript template literals using backticks (instead of using single quotes or double quotes). They allow you to use expression interpolation in the format of

`Some text here. ${expression} Some more text here.`

Just fill in with your variable or variables (or expression).

user_151729700

Here is what I tried and it executed perfectly:

CREATE OR REPLACE PROCEDURE spExample(INPUT_TABLE VARCHAR)
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    AS
    $$
    result = "";
    try {
        var sql_cmd = "SELECT * FROM IDENTIFIER(?);";
        var sql_stmt = snowflake.createStatement({sqlText: sql_cmd, binds:[INPUT_TABLE]});
        sql_stmt.execute();
    } catch(err) {
               result += "Message: " + err.message;
    }
    return result;
    $$;
Call spExample('PAIDBILLS');
user_153604870

actually yes.

Bind variable is just that, a variable. So you can do a

SELECT * FRMO MY_TABLE WHERE MY_COLUMN=?

but you can't use bind to substitute for commands or column or table names. You can however use simple JS concatenation, like

var sql_cmd = "SELECT * FROM "+INPUT_TABLE;

user_122009880
Related