我在雪花中有以下存盤程序：
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');
當我嘗試呼叫 test_procedure 時，它??給了我以下錯誤：
SQL compilation error: error line 1 at position 98 invalid identifier 'parameter'
我該如何解決？
uj5u.com熱心網友回復：
帶有單冒號 (:) 的用戶引數
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');
uj5u.com熱心網友回復：
您應該在變數名稱前加上一個冒號。
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');
下面提供了有關使用的檔案鏈接
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
uj5u.com熱心網友回復：
代碼可以簡化為單個查詢和CASE運算式：
SELECT CASE WHEN MONTHLY_DELIVERED_AMOUNT = 0 THEN NULL
ELSE monthly_target_amount
END
FROM msv_month_amount
WHERE TARGET_KEY = :parameter
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/476577.html
上一篇：如何創建一個檢查條件來檢查日期是否為特定日期，然后收入必須>x？
下一篇：Laravel資料不顯示