[Wranglers] Passing a table name as a variable to Snowflake Stored Procedure

Ali, Saqib docbook.xml at gmail.com
Sun Feb 2 22:10:03 UTC 2025


I wanted to add some exception handling to the store procedure. Here is
what I came up with so that we get meaningful error messages in case there
are any errors.

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:'||SQLROWCOUNT||' rows inserted';

EXCEPTION
  WHEN STATEMENT_ERROR THEN
    RETURN 'LOAD FAILED:'||SQLSTATE||':'||SQLCODE||':'||SQLERRM;

END;


On Thu, Jan 30, 2025 at 8:28 PM Ali, Saqib <docbook.xml at gmail.com> wrote:

> 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;
>
>
>
>
> On Wed, Jan 29, 2025 at 2:44 PM Ali, Saqib <docbook.xml at gmail.com> wrote:
>
>> 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;
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://analyticsengineering.net/pipermail/wranglers/attachments/20250202/c5ee4b63/attachment.htm>


More information about the Wranglers mailing list