Passing a table name as a variable to Snowflake stored procedure - Stack Overflow

admin管理员组

文章数量:1316000

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;

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;

• stored-procedures
• snowflake-cloud-data-platform

Share Improve this question Follow edited Jan 30 at 9:16 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Jan 29 at 23:18 Saqib AliSaqib Ali 4,4481212 gold badges6464 silver badges111111 bronze badges 1

• 1 I don't think any SQL dialects allow placeholder to be used for table or column names. You need to use dynamic SQL. I don't know the syntax for this in Snowflake; in MySQL you do it with PREPARE and EXECUTE. – Barmar Commented Jan 29 at 23:53

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 0

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

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;

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');

本文标签： Passing a table name as a variable to Snowflake stored procedureStack Overflow