snowflake-cloud-data-platform

Snowflake SQL Procedure Error: Unexpected Issue with Variable Assignment


I am creating 2 variables and assign some value to it using case when then and then insert data into a final table , getting error while executing the proc.

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;

FUND := CASE WHEN INSTR(FUND, '.file') > 0 THEN REPLACE(FUND, '.file', FUND) 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
MINUS
SELECT "FundName" AS fund
FROM "internal"."fund_dataload_status" b
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;

Solution

  • You are missing ; in your if condition

    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;
    
    FUND := CASE WHEN INSTR(FUND, '.file') > 0 THEN REPLACE(FUND, '.file', FUND) 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
        MINUS
        SELECT "FundName" AS fund
        FROM "internal"."fund_dataload_status" b;
    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;