Using IF statement with Snowflake SQL - Stack Overflow

I am trying to get a statement to run as part of an IF statement in Snowflake SQL. I originally was using it with a variable, but that seemed to not work at all. I got it running up to a point now, but it's returning the wrong result.

Basically, if a value comes back as less than 998, I want to run a large block of code. There will be another IF statement following this where if the value comes back as greater than or equal to 998, then run a smaller block of code. Here is some sample data:

CREATE OR REPLACE TEMPORARY TABLE DBO."temp_Settings" (
    "DisplayName" Varchar (50)
    ,"DisplayOrderNum" INT
    ,"DeleteInd"  Boolean
);
INSERT INTO DBO."temp_Settings" VALUES 
( 'Physician Referral', 998,0),
( 'Clinic Referral', 24,0),
( 'HMO Referral', 999,0);

CREATE OR REPLACE TEMPORARY TABLE DBO."temp_Priority" (
    "CompanyName" Varchar (25)
    ,"ContactName" Varchar(30)    
);
INSERT INTO DBO."temp_Priority" VALUES 
( 'Company A', 'Jane Doe'),
( 'Company B', 'John Smith'),
( 'Company C', 'Jim Doe');

BEGIN
    LET SqlStr varchar; 
    IF  ((SELECT MIN("DisplayOrderNum") FROM DBO."temp_Settings" WHERE "DeleteInd" = 0)<998) THEN     
        SET SqlStr := 'SELECT * FROM DBO."temp_Priority" ';    
     EXECUTE IMMEDIATE :SqlStr; 
    END IF;
    
    return 'Skip it';
END;

I have worked with many variations of this code. This one at least completes without an error, but as I mentioned above, it's returning 'Skip it' each time, when based on the sample data it should run the simple select query.

Any help would be appreciated.

I am trying to get a statement to run as part of an IF statement in Snowflake SQL. I originally was using it with a variable, but that seemed to not work at all. I got it running up to a point now, but it's returning the wrong result.

Basically, if a value comes back as less than 998, I want to run a large block of code. There will be another IF statement following this where if the value comes back as greater than or equal to 998, then run a smaller block of code. Here is some sample data:

CREATE OR REPLACE TEMPORARY TABLE DBO."temp_Settings" (
    "DisplayName" Varchar (50)
    ,"DisplayOrderNum" INT
    ,"DeleteInd"  Boolean
);
INSERT INTO DBO."temp_Settings" VALUES 
( 'Physician Referral', 998,0),
( 'Clinic Referral', 24,0),
( 'HMO Referral', 999,0);

CREATE OR REPLACE TEMPORARY TABLE DBO."temp_Priority" (
    "CompanyName" Varchar (25)
    ,"ContactName" Varchar(30)    
);
INSERT INTO DBO."temp_Priority" VALUES 
( 'Company A', 'Jane Doe'),
( 'Company B', 'John Smith'),
( 'Company C', 'Jim Doe');

BEGIN
    LET SqlStr varchar; 
    IF  ((SELECT MIN("DisplayOrderNum") FROM DBO."temp_Settings" WHERE "DeleteInd" = 0)<998) THEN     
        SET SqlStr := 'SELECT * FROM DBO."temp_Priority" ';    
     EXECUTE IMMEDIATE :SqlStr; 
    END IF;
    
    return 'Skip it';
END;

I have worked with many variations of this code. This one at least completes without an error, but as I mentioned above, it's returning 'Skip it' each time, when based on the sample data it should run the simple select query.

Any help would be appreciated.

• sql
• snowflake-cloud-data-platform

Share Improve this question Follow edited Jan 23 at 2:15 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Jan 22 at 21:24 FinChaseFinChase 1544 bronze badges 2

• "it's returning 'Skip it' each time, when based on the sample data it should run the simple select query." If you want to return result of a select query you need to use a resultset: example. LET res RESULTSET := (EXECUTE IMMEDIATE :SqlStr); return table(res); – Lukasz Szozda Commented Jan 22 at 21:35
• Why do you think it's not running the query? Maybe the return statement should be in an ELSE block so it only runs when the IF fails. – Barmar Commented Jan 22 at 21:35

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

As mentioned in the comments, if you want to return the result of a SELECT query, you need to define it as RESULTSET.

One more thing is to declare the datatype of the variables in the DECLARE section and to run an anonymous block you need to wrap it in EXECUTE IMMEDIATE $$ .

Final script

EXECUTE IMMEDIATE $$
DECLARE 
    DisplayOrderNum NUMBER;
    OUTPUT RESULTSET; 
begin
    SELECT MIN("DisplayOrderNum")  into :DisplayOrderNum FROM "temp_Settings" WHERE "DeleteInd" = FALSE;
    IF (DisplayOrderNum < 998) THEN 
        OUTPUT:=(SELECT * FROM "temp_Priority");
    END IF;
    RETURN TABLE(OUTPUT);
end;
$$
;

Output