I have following stored procedure in Snowflake:

 create or replace procedure test_procedure(parameter varchar)
    returns number
    language sql
    as
    $$
    begin
    
        if ((SELECT MONTHLY_DELIVERED_AMOUNT FROM test.process.msv_month_amount where TARGET_KEY = parameter) = 0)
        then
            return null;
        else
            return (SELECT monthly_target_amount from test.process.msv_month_amount where TARGET_KEY = parameter);
        end if;
    end;
    $$
    ;
    
    
    call test_procedure('Key10');

When I try to call the test_procedure it give me following error:

 SQL compilation error: error line 1 at position 98 invalid identifier 'parameter'

How do i fix this?

CodePudding user response：

User Parameter with single colon (:)

create or replace procedure test_procedure(parameter varchar)
    returns number
    language sql
    as
    $$
    begin
    
        if ((SELECT MONTHLY_DELIVERED_AMOUNT FROM test.process.msv_month_amount where TARGET_KEY = :parameter) = 0)
        then
            return null;
        else
            return (SELECT monthly_target_amount from test.process.msv_month_amount where TARGET_KEY = :parameter);
        end if;
    end;
    $$
    ;
    
    
    call test_procedure('Key10');

CodePudding user response：

You should prefix the variable name with a colon.

create or replace procedure test_procedure(parameter varchar)
returns number
language sql
as
$$
begin

    if ((SELECT MONTHLY_DELIVERED_AMOUNT FROM msv_month_amount where TARGET_KEY = :parameter) = 0)
    then
        return null;
    else
        return (SELECT monthly_target_amount from msv_month_amount where TARGET_KEY = :parameter);
    end if;
end;
$$
;

call test_procedure('Key10');

Documentation links on the usage are provided below

https://docs.snowflake.com/en/developer-guide/snowflake-scripting/variables.html#using-a-variable-in-a-sql-statement-binding

https://docs.snowflake.com/en/sql-reference/stored-procedures-snowflake-scripting.html#calling-a-stored-procedure-without-using-the-returned-value

CodePudding user response：

The code could be simplified to single query and CASE expression:

SELECT CASE WHEN MONTHLY_DELIVERED_AMOUNT = 0 THEN NULL
            ELSE monthly_target_amount 
       END
FROM msv_month_amount 
WHERE TARGET_KEY = :parameter