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

Question

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

AS

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

Lukasz Szozda · Accepted Answer · 2022-10-17 14:12:11Z

0

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:

answered Oct 17, 2022 at 14:12

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vizqlshrivastava Over a year ago

Thank you @Lukasz Szozda. The := was a typo. The stored procedure is now working, thanks for your inputs. Can we use ARRAY_AGG and OBJECT_CONSTRUCT_KEEP_NULL functions inside the case statement. I need to use them to present the output of the SP in a json format.

Lukasz Szozda Over a year ago

@VishalShrivastava Yes, you could return single VARIANT column instead of table.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related