Snowflake存储过程变量赋值异常问题求助

问题点1：REPLACE函数逻辑错误 #

原代码中第一个CASE WHEN的REPLACE用法逻辑错误：

FUND := CASE WHEN INSTR(FUND, '.file') > 0 THEN REPLACE(FUND, '.file', FUND) ELSE FUND END;

该语句会将字符串中的.file替换为FUND本身，导致字符串重复（例如test.file会变成testtest.file），不符合“移除.file后缀”的预期，应改为将.file替换为空字符串。

问题点2：MINUS子查询列数不匹配 #

当FUND='All'时，INSERT的SELECT语句返回3列，但MINUS后的子查询仅返回1列。Snowflake要求MINUS/EXCEPT两边的列数、数据类型必须完全一致，这会直接触发执行错误。应改用WHERE NOT EXISTS来过滤已存在的FundName，避免列数不匹配问题。

修正后的完整代码 #

create or REPLACE PROCEDURE "internal"."ivp_polaris_MonthEnd"(
"FUND" varchar(255),"LOADTYPE" VARCHAR(50))
RETURNS VARCHAR(16777216)
LANGUAGE SQL
EXECUTE AS OWNER
as
Begin

IF (FUND IS NULL OR FUND ='')
THEN
RETURN 'FUND is null.';
END IF;

-- 修正REPLACE逻辑，移除.file后缀
FUND := CASE WHEN INSTR(FUND, '.file') > 0 THEN REPLACE(FUND, '.file', '') ELSE FUND END;
FUND := CASE WHEN FUND = 'SEGAM%' THEN 'SGAM%' ELSE FUND END;


IF (FUND = 'All') THEN
INSERT INTO "internal"."fund_dataload_status" ("FundName","created_on","LoadType")
SELECT  fund, CURRENT_TIMESTAMP(), :LoadType
FROM (
SELECT "Fund Name" AS fund
FROM "dimension"."fund_feeder_master_non_series"
UNION
SELECT "Feeder Master Name" AS fund
FROM "dimension"."fund_feeder_master_non_series"
) a
-- 使用WHERE NOT EXISTS替代MINUS，避免列数不匹配
WHERE NOT EXISTS (
    SELECT 1 FROM "internal"."fund_dataload_status" b WHERE b."FundName" = a.fund
);
ELSE
INSERT INTO "internal"."fund_dataload_status" ("FundName","created_on","LoadType")
VALUES(:FUND, CURRENT_TIMESTAMP(), :LoadType);
END IF;

DELETE FROM "internal"."fund_dataload_status"
WHERE "FundName" IS NULL OR "FundName" = '';

RETURN 'Procedure completed.';
END;

额外说明 #

• 若FUND = 'SEGAM%'是要匹配包含SEGAM的模糊查询，需将=改为LIKE，否则仅会精确匹配字符串SEGAM%。
• 建议在执行存储过程前，先单独测试FUND='All'分支的SELECT语句，验证返回的FundName是否符合预期。

内容的提问来源于stack exchange，提问作者Mohit Sharma