[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