Passing a table name as a variable to Snowflake stored procedure

Question

I am trying to define a stored procedure in Snowflake as follows. But it is erroring out on the variable binding for :table_name.

Error: `Syntax error: unexpected ‘into’

How do I achieve this?

CREATE OR REPLACE PROCEDURE SP__INSERT_TO_CUSTOMERS(table_name varchar)
RETURNS string
LANGUAGE SQL
AS

BEGIN

insert into :table_name (customer_name)
select distinct
customer_name
from orders;

RETURN ‘SUCCESS’;
END;

Answer 1

In Snowflake, when using dynamic table names in stored procedures, you need to use the EXECUTE IMMEDIATE statement since table names can't be parameterized directly using bind variables. Here's the corrected version:

CREATE OR REPLACE PROCEDURE SP__INSERT_TO_CUSTOMERS(table_name varchar)
RETURNS string
LANGUAGE SQL
AS
$$
BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO ' || :table_name || ' (customer_name)
                      SELECT DISTINCT customer_name 
                      FROM orders';
    RETURN 'SUCCESS';
END;
$$;

Key changes made:

1. Used EXECUTE IMMEDIATE to construct and execute the dynamic SQL
2. Concatenated the table name parameter with the rest of the SQL statement
3. Added $$ delimiter marks which are required for stored procedures in Snowflake

You can then call the procedure like this:

CALL SP__INSERT_TO_CUSTOMERS('your_table_name');

Answer 2

Looks like for table names we have to use the IDENTIFIER() keyword as follows:

CREATE OR REPLACE PROCEDURE SP__INSERT_TO_CUSTOMERS(table_name varchar)
RETURNS string
LANGUAGE SQL
AS

BEGIN

insert into IDENTIFIER(:table_name) (customer_name)
select distinct
customer_name
from orders;

RETURN ‘SUCCESS’;
END;

Answer 3

Like @Barmar mentioned in comment, you can define a variable where you can prepare your sql statement and then execute it using EXECUTE IMMEDIATE.

Example procedure which I tested by creating a sample order and customer table

CREATE OR REPLACE PROCEDURE SP__INSERT_TO_CUSTOMERS(table_name VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    LET sql_insert STRING;
    
    sql_insert := 'INSERT INTO ' || table_name || ' (customer_name)
                SELECT DISTINCT customer_name FROM orders;';
    
    EXECUTE IMMEDIATE sql_insert;
    
    RETURN 'SUCCESS';
END;
$$;

Call the procedure

Check whether data is inserted in Customers