CODEWITHSUNDEEP
snowflake-cloud-data-platform
sirisha
sirisha

Reputation: 51

Simple Stored procedure in Snowflake Scripting

I need to create a table in snowflake stored procedure using sql. Below is the code

create or replace procedure sp(tmp_table varchar,col_name varchar,d_type varchar ) 
returns varchar not null
as
$$
BEGIN
    drop table if exists identifier(:tmp_table);
    create table identifier(:tmp_table) (identifier(:col_name)  identifier(:d_type));
END;
$$;

I am getting the error as

syntax error line 4 at position 24 unexpected '('. (line 4)

Could you please help me on this issue?

Upvotes: 3

Views: 533

Answers (2)

James Goodhand
James Goodhand

Reputation: 88

Unfortunately, it isn't possible to dynamically name columns in this way using Snowflake Scripting [1]. As an alternative you can dynamically generate your SQL statements as text to then execute.

I've swapped the drop table for create or replace as it does the same function, but in one command.

create or replace procedure sp(tmp_table varchar, col_name varchar, d_type varchar) 
returns table (result varchar)
language sql
as
DECLARE
    sql_text varchar;
    rs resultset;
    
    invalid_input EXCEPTION (-20001, 'Input contains whitespace.');
BEGIN
    IF ((SELECT :tmp_table regexp '(^\\S*$)')=FALSE) THEN
        RAISE invalid_input;
    END IF;
    
    IF ((SELECT :col_name regexp '(^\\S*$)')=FALSE) THEN
        RAISE invalid_input;
    END IF;
    
    IF ((SELECT :d_type regexp '(^\\S*$)')=FALSE) THEN
        RAISE invalid_input;
    END IF;

    sql_text := 'create or replace table ' || :tmp_table || '(' || :col_name || ' ' || :d_type || ')' ;
    rs := (execute immediate :sql_text);
    return table(rs);
END;

Note: In the example above I've included some code to check for whitespace in the inputs to minimise the potential for SQL injection. This is important to stop users abusing the procedure. Additional checks would be prudent. You should also ensure that this Stored Procedure runs as the least privilege role possible to further minimise the scope for abuse.

Example as JavaScript (without SQL injection protection):

CREATE OR REPLACE procedure sp(TMP_TABLE varchar, COL_NAME varchar, D_TYPE varchar) 
    RETURNS varchar not null
    LANGUAGE javascript
    AS
    $$
        var sql_cmd = "DROP TABLE IF EXISTS " + TMP_TABLE + ";";
        var stmt = snowflake.createStatement(
            {sqlText: sql_cmd}
        );
        var res = stmt.execute();
        
        sql_cmd = "CREATE TABLE " + TMP_TABLE + " (" + COL_NAME + " " + D_TYPE + ");";
        stmt = snowflake.createStatement(
            {sqlText: sql_cmd}
        );
        res = stmt.execute();
        res.next();
        return res.getColumnValue(1);
    $$
;

Upvotes: 0

Gokhan Atil
Gokhan Atil

Reputation: 10039

Bind variables are not supported in columns, this is why your script fails. You can use EXECUTE IMMEDIATE to generate a dynamic SQL to overcome this issue:

create or replace procedure sp(tmp_table varchar,col_name varchar,d_type varchar ) 
returns varchar not null
as
$$
BEGIN
    drop table if exists identifier(:tmp_table);
    execute immediate 'create table ' || :tmp_table || '(' || :col_name || ' ' || :d_type || ')' ;
END;
$$;

Upvotes: 2

Related Questions