最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

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

programmeradmin2浏览0评论

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;
Share Improve this question edited Jan 30 at 9:16 marc_s 756k184 gold badges1.4k silver badges1.5k bronze badges asked Jan 29 at 23:18 Saqib AliSaqib Ali 4,44812 gold badges64 silver badges111 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

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');
发布评论

评论列表(0)

  1. 暂无评论