Can we use parameters in case statement in snowflake sql stored procedure-CodePudding

I am working on a stored procedure in snowflake using sql language which expects three parameters. I need to execute different sql queries based on the parameter value of the third parameter. For eg, if the 3rd parameter is A, then execute sql_query1. If 3rd parameter is B, then execute sql_query2 and so on. Is there a way to do this in snowflake stored procedure using sql language and not javascript. Also, the parameters of the stored procedure will be used in the where clause to filter the data. Below is what I've done till now, however I'm getting syntax error:

create or replace procedure CASE_DEMO_CMS(CONTRACT_ID VARCHAR, VALID_FROM_TSTP TIMESTAMP_NTZ(9),product_System_code varchar)

RETURNS TABLE ()

LANGUAGE SQL

declare

   res resultset;

    query varchar;

begin

    case

        when :product_system_code :='U2' then

                    query := 'SELECT

                        .......


                    WHERE ctrt.contract_id = ?

                    AND ctrt.valid_from_tstp = ?

                    AND ctrt.product_system_code= ?';

    res := (execute immediate :query using(contract_id,valid_from_tstp,product_system_code));

    return table(res);

else return 'Unspecified value'

end case;

end;

Error: Syntax error: unexpected when

CodePudding user response：

The stored procedure should return the same output for each return part and the comparision operator is =(not the :=):

CREATE OR REPLACE procedure CASE_DEMO_CMS(
                CONTRACT_ID VARCHAR,
                VALID_FROM_TSTP TIMESTAMP_NTZ(9),
                product_System_code varchar)
RETURNS TABLE (col TEXT, col2 INT)
LANGUAGE SQL
AS
declare
   res resultset;
   query varchar;
begin
    case when :product_system_code ='U2' 
    then
         query := 'SELECT ctrt.some_col, ctrt.col2
                   FROM ctrt
                   WHERE ctrt.contract_id = ?
                   AND ctrt.valid_from_tstp = ?
                   AND ctrt.product_system_code= ?';
         res := (execute immediate :query 
                using(contract_id,valid_from_tstp,product_system_code));
         return table(res);
    else 
         query := 'SELECT ''Unspecified value'', NULL::INT';
         res := (execute immediate :query);
         return table(res);
    end case;
end;

Sample data:

CREATE OR REPLACE TABLE ctrt
AS
SELECT 1 AS contract_id, CURRENT_DATE() AS valid_from_tstp,
       'U2' AS product_system_code, 'text' AS some_col, 10 AS col2;

Call:

CALL CASE_DEMO_CMS(1, CURRENT_DATE(), 'U2');

Output:

Call:

CALL CASE_DEMO_CMS(1, CURRENT_DATE(), 'Some other value');

Output: