Snowflake stored procedure passing parameter

Question

I am trying to call a stored procedure in Snowflake and getting the following error message

invalid identifier 'ID_1PAR' (line 2)

I created the following stored procedure, and expecting the parameters in the call to the stored procedure will get inserted into the table

CALL SP_BRANDS(123, 'Important note', 'New Product', 150, 75);

Code:

CREATE OR REPLACE PROCEDURE PUBLIC.SP_BRANDS
    (ID_1PAR NUMBER(38,0), 
     NOTESPAR VARCHAR(50), 
     PRODUCTPAR VARCHAR(50), 
     KPI1PAR NUMBER(38,0), 
     KPI2PAR NUMBER(38,0))
RETURNS VARCHAR(50)
LANGUAGE SQL
EXECUTE AS OWNER
AS '
BEGIN
    INSERT INTO BRANDS (ID_1, NOTES, PRODUCT, KPI1, KPI2)
    VALUES (ID_1PAR, NOTESPAR, PRODUCTPAR, KPI1PAR, KPI2PAR);  

    RETURN ''Insert Successful'';
END;
';

Answer 1

In order to bind the variable value in SQL Scripting, you need to use colon (:) followed by variable name. your updated stored procedure should be as below.

CREATE OR REPLACE PROCEDURE PUBLIC.SP_BRANDS(ID_1PAR NUMBER(38,0), NOTESPAR VARCHAR(50), PRODUCTPAR VARCHAR(50),
KPI1PAR NUMBER(38,0), KPI2PAR NUMBER(38,0))
RETURNS VARCHAR(50)
LANGUAGE SQL
EXECUTE AS OWNER
AS '
BEGIN
    INSERT INTO BRANDS ("ID_1", "NOTES", "PRODUCT", "KPI1", "KPI2")
    VALUES (:ID_1PAR, :NOTESPAR, :PRODUCTPAR, :KPI1PAR, :KPI2PAR);  

    RETURN ''Insert Successful'';
END;
';

Please refer Snowflake documentation here - https://docs.snowflake.com/en/developer-guide/snowflake-scripting/variables#using-a-variable-in-a-sql-statement-binding