Search code examples
stored-proceduressnowflake-cloud-data-platform

Passing a table name as a variable to Snowflake stored procedure

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;
Solution

  • 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;