SnowConvert AI - Redshift - CREATE PROCEDURE¶

 CREATE PROCEDURE TEST_PROCEDURE ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/07/2025",  "domain": "test" }}'
AS
$$
    BEGIN
        NULL;
    END;
$$;

 CREATE OR REPLACE PROCEDURE test_procedure (integer)
LANGUAGE plpgsql
AS
$$
DECLARE
    first_alias ALIAS  FOR $1;
    second_alias ALIAS  FOR $1;
BEGIN
   INSERT INTO t1
   VALUES (first_alias + 1);
   INSERT INTO t1
   VALUES (second_alias + 2);
END;
$$;

--Notice the parameter already has a name
--and we are defining two alias to the same parameter
CREATE OR REPLACE PROCEDURE test_procedure (PARAMETER1 integer)
LANGUAGE plpgsql
AS
$$
DECLARE
    first_alias ALIAS  FOR $1;
    second_alias ALIAS  FOR $1;
BEGIN
   INSERT INTO t1
   VALUES (first_alias + 1);
   INSERT INTO t1
   VALUES (second_alias + 2);
END;
$$;

 CREATE OR REPLACE PROCEDURE test_procedure (SC_ARG1 integer)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS
$$
BEGIN
   INSERT INTO t1
   VALUES (:SC_ARG1 + 1);
   INSERT INTO t1
   VALUES (:SC_ARG1 + 2);
END;
$$;

--Notice the parameter already has a name
--and we are defining two alias to the same parameter
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "t1" **
CREATE OR REPLACE PROCEDURE test_procedure (PARAMETER1 integer)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS
$$
BEGIN
   INSERT INTO t1
   VALUES (:PARAMETER1 + 1);
   INSERT INTO t1
   VALUES (:PARAMETER1 + 2);
END;
$$;

CREATE OR REPLACE PROCEDURE SP_PARAMS(
IN PARAM1 INTEGER,
OUT PARAM2 INTEGER,
INOUT PARAM3 INTEGER)
AS 
$$
    BEGIN
        NULL;
    END;
$$ 
LANGUAGE plpgsql;

 CREATE OR REPLACE PROCEDURE SP_PARAMS (PARAM1 INTEGER, PARAM2 OUT INTEGER, PARAM3 OUT INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/10/2025",  "domain": "no-domain-provided" }}'
AS
$$
    BEGIN
        NULL;
    END;
$$;

 CREATE OR REPLACE PROCEDURE SP()
AS 
$somename$
BEGIN
   NULL;
END;
$somename$ 
LANGUAGE plpgsql;

 CREATE OR REPLACE PROCEDURE SP ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/07/2025",  "domain": "test" }}'
AS
$$
   BEGIN
      NULL;
   END;
$$;

 CREATE OR REPLACE PROCEDURE MY_PROCEDURE() 
AS 
$$
    BEGIN
        NULL;
    END;
$$ 
LANGUAGE plpgsql;

 CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/10/2025",  "domain": "test" }}'
AS
$$
    BEGIN
        NULL;
    END;
$$;

 CREATE OR REPLACE PROCEDURE first_procedure (first_parameter integer)
LANGUAGE plpgsql
    AS
$$
DECLARE
    i int := first_parameter;
BEGIN
   select i;
END;
$$;

CREATE OR REPLACE PROCEDURE second_procedure (first_parameter integer)
LANGUAGE plpgsql
    AS
$$
DECLARE
    i int := first_parameter;
DECLARE
    j int := first_parameter;
BEGIN
   select i;
END;
$$;

 CREATE OR REPLACE PROCEDURE first_procedure (first_parameter integer)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/11/2025",  "domain": "test" }}'
    AS
$$
   DECLARE
      i int := first_parameter;
BEGIN
   select i;
END;
$$;

CREATE OR REPLACE PROCEDURE second_procedure (first_parameter integer)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/11/2025",  "domain": "test" }}'
    AS
$$
   DECLARE
      i int := first_parameter;
      j int := first_parameter;
BEGIN
   select i;
END;
$$;

 CREATE OR REPLACE PROCEDURE update_employee_sp() AS
$$
BEGIN
    select var;
EXCEPTION WHEN OTHERS THEN
    RAISE INFO 'An exception occurred.';
END;
$$
LANGUAGE plpgsql;

 CREATE OR REPLACE PROCEDURE update_employee_sp ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS
$$
BEGIN
    select var;
EXCEPTION WHEN OTHER THEN
        CALL RAISE_MESSAGE_UDF('INFO', 'An exception occurred.');
        RAISE;
END;
$$;

 CREATE OR REPLACE PROCEDURE test_procedure (first_parameter integer)
LANGUAGE plpgsql
AS
$$
    <<Begin_block_label>>
BEGIN
   INSERT INTO my_test_table
   VALUES (first_parameter);
END;
$$;

 CREATE OR REPLACE PROCEDURE test_procedure (first_parameter integer)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS
$$
   !!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<Begin_block_label>> ***/!!!
BEGIN
   INSERT INTO my_test_table
   VALUES (:first_parameter);
END;
$$;

 CREATE OR REPLACE PROCEDURE SP_NONATOMIC()
NONATOMIC 
AS 
$$
    BEGIN
        NULL;
    END;
$$ 
LANGUAGE plpgsql;

 CREATE OR REPLACE PROCEDURE SP_NONATOMIC ()
RETURNS VARCHAR
----** SSC-FDM-RS0008 - SNOWFLAKE USES AUTOCOMMIT BY DEFAULT. **
--NONATOMIC
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/10/2025",  "domain": "test" }}'
AS
$$
    BEGIN
        NULL;
    END;
$$;

 CREATE OR REPLACE PROCEDURE SP_POSITIONAL_REFERENCES(
INTEGER,
param2 INTEGER,
INTEGER)
AS 
$$
    DECLARE
        localVariable INTEGER := 0;
    BEGIN
        localVariable := $2 + $3 + $1;
    END;
$$ 
LANGUAGE plpgsql;

 CREATE OR REPLACE PROCEDURE SP_POSITIONAL_REFERENCES (SC_ARG1
INTEGER,
param2 INTEGER, SC_ARG3 INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS
$$
    DECLARE
        localVariable INTEGER := 0;
    BEGIN
        localVariable := param2 + SC_ARG3 + SC_ARG1;
    END;
$$;

 CREATE OR REPLACE PROCEDURE raise_example(IN user_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
	RAISE EXCEPTION 'User % not exists.', user_id;
END;
$$;

 CREATE OR REPLACE PROCEDURE raise_example (user_id INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/11/2025",  "domain": "test" }}'
AS $$
BEGIN
	CALL RAISE_MESSAGE_UDF('EXCEPTION', 'User % not exists.', array_construct(:user_id));
END;
$$;

 CREATE OR REPLACE PROCEDURE RAISE_MESSAGE_UDF(LEVEL VARCHAR, MESSAGE VARCHAR, ARGS VARIANT)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    DECLARE
        MY_EXCEPTION EXCEPTION (-20002, 'To view the EXCEPTION MESSAGE, you need to check the log.');
        SC_RAISE_MESSAGE VARCHAR;
    BEGIN
        SC_RAISE_MESSAGE := STRING_FORMAT_UDF(MESSAGE, ARGS);
        IF (LEVEL = 'EXCEPTION') THEN
            SYSTEM$LOG_ERROR(SC_RAISE_MESSAGE);
            RAISE MY_EXCEPTION;
        ELSEIF (LEVEL = 'WARNING') THEN
            SYSTEM$LOG_WARN(SC_RAISE_MESSAGE);
            RETURN 'Warning printed successfully';
        ELSE
            SYSTEM$LOG_INFO(SC_RAISE_MESSAGE);
            RETURN 'Message printed successfully';
        END IF;
    END;
$$;

 CREATE OR REPLACE FUNCTION PUBLIC.STRING_FORMAT_UDF(PATTERN VARCHAR, ARGS VARIANT)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "udf",  "convertedOn": "02/11/2025",  "domain": "test" }}'
AS
$$
	var placeholder_str = "{%}";
	var result = PATTERN.replace(/(?<!%)%(?!%)/g, placeholder_str).replace("%%","%");
	for (var i = 0; i < ARGS.length; i++)
	{
		result = result.replace(placeholder_str, ARGS[i]);
	}
	return result;
$$;

 CREATE OR REPLACE PROCEDURE procedure1 ()
AS
$$
BEGIN
   RETURN;
END
$$ LANGUAGE plpgsql;

 CREATE OR REPLACE PROCEDURE procedure1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/12/2025",  "domain": "test" }}'
AS
$$
BEGIN
  RETURN NULL;
END
$$;

 CREATE OR REPLACE PROCEDURE procedure1 (OUT output_value VARCHAR)
AS
$$
BEGIN
   RETURN;
END
$$ LANGUAGE plpgsql;

 CREATE OR REPLACE PROCEDURE procedure1 (output_value OUT VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
AS
$$
BEGIN
   RETURN NULL;
END
$$;

 CREATE OR REPLACE PROCEDURE SP_SECURITY_INVOKER( )
AS 
$$
    BEGIN
        NULL;
    END;
$$ 
LANGUAGE plpgsql
SECURITY INVOKER
;

CREATE OR REPLACE PROCEDURE SP_SECURITY_DEFINER( )
AS 
$$
     BEGIN
        NULL;
    END;
$$ 
LANGUAGE plpgsql
SECURITY DEFINER;

 CREATE OR REPLACE PROCEDURE SP_SECURITY_INVOKER ( )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/07/2025",  "domain": "test" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        NULL;
    END;
$$
;

CREATE OR REPLACE PROCEDURE SP_SECURITY_DEFINER ( )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/07/2025",  "domain": "test" }}'
EXECUTE AS OWNER
AS
$$
    BEGIN
        NULL;
    END;
$$;

 CREATE OR REPLACE PROCEDURE VARIABLE_DECLARATION()
LANGUAGE plpgsql
AS $$
DECLARE
    v_simple_int INT;	
    v_default_char CHAR(4) DEFAULT 'ABCD';
    v_default_float FLOAT := 10.00;
    v_constant_char CONSTANT CHAR(4) := 'ABCD';
    v_notnull VARCHAR NOT NULL DEFAULT 'Test default';
    v_refcursor REFCURSOR;
BEGIN
-- Procedure logic
END;
$$;

 CREATE OR REPLACE PROCEDURE VARIABLE_DECLARATION ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS $$
        DECLARE
            v_simple_int INT;
            v_default_char CHAR(4) DEFAULT 'ABCD';
            v_default_float FLOAT := 10.00;
            v_constant_char CHAR(4) := 'ABCD';
            --** SSC-FDM-PG0012 - NOT NULL CONSTRAINT HAS BEEN REMOVED. ASSIGNING NULL TO THIS VARIABLE WILL NO LONGER CAUSE A FAILURE. **
            v_notnull VARCHAR DEFAULT 'Test default';
            v_refcursor RESULTSET;
BEGIN
            NULL;
-- Procedure logic
END;
$$;

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

 COMMIT TRANSACTION;

 COMMIT;

 CREATE OR REPLACE PROCEDURE transaction_test(a INT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test VALUES (a);
    COMMIT;
    INSERT INTO transaction_values_test VALUES (a + 1);
    COMMIT;
END
$$;

CALL transaction_test(120);

SELECT * FROM transaction_values_test;

 CREATE OR REPLACE PROCEDURE transaction_test (a INT)
RETURNS VARCHAR
    LANGUAGE SQL
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    VALUES (:a);
    COMMIT;
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    VALUES (:a + 1);
    COMMIT;
END
$$;

CALL transaction_test(120);

SELECT * FROM
    transaction_values_test;

 CREATE OR REPLACE PROCEDURE nonatomic_procedure(a int)
    NONATOMIC
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a + 2);
    INSERT INTO transaction_values_test values (a + 3);
    COMMIT;
END
$$;

CALL nonatomic_procedure(10);

SELECT * FROM transaction_values_test;

 CREATE OR REPLACE PROCEDURE nonatomic_procedure (a int)
RETURNS VARCHAR
--    --** SSC-FDM-RS0008 - SNOWFLAKE USES AUTOCOMMIT BY DEFAULT. **
--    NONATOMIC
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    INSERT INTO transaction_values_test
    values (:a + 2);
    INSERT INTO transaction_values_test
    values (:a + 3);
    COMMIT;
END
$$;

CALL nonatomic_procedure(10);

SELECT * FROM
transaction_values_test;

 CREATE OR REPLACE PROCEDURE transaction_test(a INT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test VALUES (a);
    COMMIT;
END
$$;

CREATE OR REPLACE PROCEDURE nested_transaction_test(a INT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    INSERT INTO transaction_values_test values (a + 1);
    INSERT INTO transaction_values_test values (a + 2);
    CALL transaction_test(a + 3);
END
$$;

 CREATE OR REPLACE PROCEDURE transaction_test (a INT)
RETURNS VARCHAR
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    VALUES (:a);
    COMMIT;
END
$$;

CREATE OR REPLACE PROCEDURE nested_transaction_test (a INT)
RETURNS VARCHAR
    LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    INSERT INTO transaction_values_test
    values (:a);
    INSERT INTO transaction_values_test
    values (:a + 1);
    INSERT INTO transaction_values_test
    values (:a + 2);
    --** SSC-FDM-RS0006 - CALLED PROCEDURE CONTAINS USAGES OF COMMIT/ROLLBACK, MODIFYING THE CURRENT TRANSACTION IN CHILD SCOPES IS NOT SUPPORTED IN SNOWFLAKE **
    CALL transaction_test(:a + 3);
END
$$;

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

 ROLLBACK TRANSACTION;

 ROLLBACK;

 CREATE OR REPLACE PROCEDURE transaction_test(a INT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    COMMIT;
    insert into transaction_values_test values (80);
    insert into transaction_values_test values (55);
    ROLLBACK;
END
$$;

CALL transaction_test(120);

SELECT * FROM transaction_values_test;

 CREATE OR REPLACE PROCEDURE transaction_test (a INT)
RETURNS VARCHAR
    LANGUAGE SQL
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test values (:a);
    COMMIT;
    BEGIN TRANSACTION;
    insert into transaction_values_test values (80);
    insert into transaction_values_test values (55);
    ROLLBACK;
END
$$;

CALL transaction_test(120);

SELECT * FROM
    transaction_values_test;

 CREATE OR REPLACE PROCEDURE nonatomic_procedure(a int)
    NONATOMIC
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    INSERT INTO transaction_values_test values (a + 1);
    ROLLBACK;
    INSERT INTO transaction_values_test values (a + 2);
    INSERT INTO transaction_values_test values (a + 3);
    COMMIT;
END
$$;

CALL nonatomic_procedure(10);

SELECT * FROM transaction_values_test;

 CREATE OR REPLACE PROCEDURE nonatomic_procedure (a int)
RETURNS VARCHAR
--    --** SSC-FDM-RS0008 - SNOWFLAKE USES AUTOCOMMIT BY DEFAULT. **
--    NONATOMIC
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    INSERT INTO transaction_values_test
    values (:a);
    INSERT INTO transaction_values_test
    values (:a + 1);
    ROLLBACK;
    INSERT INTO transaction_values_test
    values (:a + 2);
    INSERT INTO transaction_values_test
    values (:a + 3);
    COMMIT;
END
$$;

CALL nonatomic_procedure(10);

SELECT * FROM
transaction_values_test;

 CREATE OR REPLACE PROCEDURE transaction_test(a int)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    ROLLBACK;
    INSERT INTO transaction_values_test values (a + 1);
END
$$;

CREATE OR REPLACE PROCEDURE nested_transaction_test(a int)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    CALL transaction_test(a + 3);
    COMMIT;
END
$$;

 CREATE OR REPLACE PROCEDURE transaction_test (a int)
RETURNS VARCHAR
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    values (:a);
    ROLLBACK;
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    values (:a + 1);
    COMMIT;
END
$$;

CREATE OR REPLACE PROCEDURE nested_transaction_test (a int)
RETURNS VARCHAR
    LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    values (:a);
    --** SSC-FDM-RS0006 - CALLED PROCEDURE CONTAINS USAGES OF COMMIT/ROLLBACK, MODIFYING THE CURRENT TRANSACTION IN CHILD SCOPES IS NOT SUPPORTED IN SNOWFLAKE **
    CALL transaction_test(:a + 3);
    COMMIT;
END
$$;

 CREATE OR REPLACE PROCEDURE rollback_ddl(a int)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    CREATE TABLE someRollbackTable
    (
        col1 INTEGER
    );

    INSERT INTO someRollbackTable values (a);
    ROLLBACK;
END
$$;

 CREATE OR REPLACE PROCEDURE rollback_ddl (a int)
RETURNS VARCHAR
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    values (:a);
    CREATE TABLE someRollbackTable
    (
        col1 INTEGER
    );
    BEGIN TRANSACTION;
    INSERT INTO someRollbackTable
    values (:a);
    --** SSC-FDM-RS0007 - DDL STATEMENTS PERFORM AN AUTOMATIC COMMIT, ROLLBACK WILL NOT WORK AS EXPECTED **
    ROLLBACK;
END
$$;

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

 CREATE OR REPLACE PROCEDURE truncate_in_procedure(a int)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test VALUES (a);
    TRUNCATE TABLE transaction_values_test;
    INSERT INTO transaction_values_test VALUES (a + 12);
    COMMIT;
END
$$;

CALL truncate_in_procedure(10);

SELECT * FROM transaction_values_test;

 CREATE OR REPLACE PROCEDURE truncate_in_procedure (a int)
RETURNS VARCHAR
    LANGUAGE SQL
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    VALUES (:a);
    TRUNCATE TABLE transaction_values_test;
    COMMIT;
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    VALUES (:a + 12);
    COMMIT;
END
$$;

CALL truncate_in_procedure(10);

SELECT * FROM
    transaction_values_test;

 CREATE OR REPLACE PROCEDURE nonatomic_procedure(a int)
    NONATOMIC
    LANGUAGE plpgsql
    AS $$
BEGIN
    TRUNCATE TABLE transaction_values_test;
    INSERT INTO transaction_values_test values (a);
    INSERT INTO transaction_values_test values (a + 1);
    ROLLBACK;
    INSERT INTO transaction_values_test values (a + 2);
    INSERT INTO transaction_values_test values (a + 3);
    COMMIT;
END
$$;

CALL nonatomic_procedure(10);

SELECT * FROM transaction_values_test;

 CREATE OR REPLACE PROCEDURE nonatomic_procedure (a int)
RETURNS VARCHAR
--    --** SSC-FDM-RS0008 - SNOWFLAKE USES AUTOCOMMIT BY DEFAULT. **
--    NONATOMIC
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    TRUNCATE TABLE transaction_values_test;
    INSERT INTO transaction_values_test
    values (:a);
    INSERT INTO transaction_values_test
    values (:a + 1);
    ROLLBACK;
    INSERT INTO transaction_values_test
    values (:a + 2);
    INSERT INTO transaction_values_test
    values (:a + 3);
    COMMIT;
END
$$;

CALL nonatomic_procedure(10);

SELECT * FROM
transaction_values_test;

 CREATE OR REPLACE PROCEDURE transaction_test(a INT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test VALUES (a);
    TRUNCATE TABLE transaction_values_test;
END
$$;

CREATE OR REPLACE PROCEDURE nested_transaction_test(a INT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    INSERT INTO transaction_values_test values (a + 1);
    INSERT INTO transaction_values_test values (a + 2);
    CALL transaction_test(a + 3);
END
$$;

 CREATE OR REPLACE PROCEDURE transaction_test (a INT)
RETURNS VARCHAR
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    VALUES (:a);
    TRUNCATE TABLE transaction_values_test;
    COMMIT;
END
$$;

CREATE OR REPLACE PROCEDURE nested_transaction_test (a INT)
RETURNS VARCHAR
    LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    INSERT INTO transaction_values_test
    values (:a);
    INSERT INTO transaction_values_test
    values (:a + 1);
    INSERT INTO transaction_values_test
    values (:a + 2);
    --** SSC-FDM-RS0006 - CALLED PROCEDURE CONTAINS USAGES OF COMMIT/ROLLBACK, MODIFYING THE CURRENT TRANSACTION IN CHILD SCOPES IS NOT SUPPORTED IN SNOWFLAKE **
    CALL transaction_test(:a + 3);
END
$$;

 CREATE OR REPLACE PROCEDURE proc1(x INT)
LANGUAGE plpgsql
AS $$
BEGIN
  CASE x
WHEN 1, 2 THEN
  NULL;
ELSE
  NULL;
END CASE;                  
END;
$$;

 CREATE OR REPLACE PROCEDURE proc1 (x INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/14/2025",  "domain": "test" }}'
AS $$
BEGIN
  CASE x
    WHEN 1 THEN
      NULL;
    WHEN 2 THEN
      NULL;
   ELSE
     NULL;
  END CASE;
END;
$$;

 CREATE PROCEDURE PROC1 (paramNumber int)
LANGUAGE plpgsql
AS $$
DECLARE
    result VARCHAR(100);	
BEGIN
CASE
  WHEN paramNumber BETWEEN 0 AND 10 THEN
    result := 'value is between zero and ten';
  WHEN paramNumber BETWEEN 11 AND 20 THEN
    result := 'value is between eleven and twenty';
  END CASE;  
END;
$$;

 CREATE PROCEDURE PROC1 (paramNumber int)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
    DECLARE
      result VARCHAR(100);
      case_not_found EXCEPTION (-20002, 'Case not found.');
BEGIN
CASE
  WHEN paramNumber BETWEEN 0 AND 10 THEN
    result := 'value is between zero and ten';
  WHEN paramNumber BETWEEN 11 AND 20 THEN
    result := 'value is between eleven and twenty';
  ELSE
    RAISE case_not_found;
  END CASE;
END;
$$;

 CREATE OR REPLACE PROCEDURE procedure1 (input_value INT)
AS $$
BEGIN
  CASE input_value
  WHEN 1 THEN
   NULL;
  END CASE;
END;
$$ LANGUAGE plpgsql;

 CREATE OR REPLACE PROCEDURE procedure1 (input_value INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS $$
    DECLARE
      case_not_found EXCEPTION (-20002, 'Case not found.');
BEGIN
  CASE input_value
  WHEN 1 THEN
   NULL;
  ELSE
   RAISE case_not_found;
  END CASE;
END;
$$;

 CREATE PROCEDURE PROC1 (paramNumber int)
LANGUAGE plpgsql
AS $$
DECLARE
    result VARCHAR(100);	
BEGIN
    IF paramNumber = 0 THEN
      result := 'zero';
    ELSIF paramNumber > 0 THEN
      result := 'positive';
    ELSIF paramNumber < 0 THEN
      result := 'negative';
    ELSE
      result := 'NULL';
    END IF;
END;
$$;

 CREATE PROCEDURE PROC1 (paramNumber int)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
        DECLARE
            result VARCHAR(100);
BEGIN
            IF (:paramNumber = 0) THEN
                result := 'zero';
            ELSEIF (:paramNumber > 0) THEN
                result := 'positive';
            ELSEIF (:paramNumber < 0) THEN
                result := 'negative';
              ELSE
                result := 'NULL';
            END IF;
END;
$$;

 CREATE OR REPLACE PROCEDURE procedure1 (x INT)
    LANGUAGE plpgsql
AS $$
DECLARE
    i INTEGER := 0;
BEGIN
    <<simple_loop_when>>
    LOOP
        i := i + 1;
        CONTINUE WHEN i = 5;
        RAISE INFO 'i %', i;
        EXIT simple_loop_when WHEN (i >= x);
    END LOOP;
END;
$$;

CREATE OR REPLACE PROCEDURE procedure11 (x INT)
    LANGUAGE plpgsql
AS $$
DECLARE
    i INTEGER := 0;
BEGIN
    LOOP
        i := i + 1;
		IF (I = 5) THEN 
        	CONTINUE;
		END IF;
        RAISE INFO 'i %', i;
        EXIT WHEN (i >= x);
    END LOOP;
END;
$$;

 CREATE OR REPLACE PROCEDURE procedure1 (x INT)
RETURNS VARCHAR
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
    		DECLARE
    			i INTEGER := 0;
BEGIN
    			--** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
    LOOP
        i := i + 1;
        IF (:i = 5) THEN
        	CONTINUE;
        END IF;
        CALL RAISE_MESSAGE_UDF('INFO', 'i %', array_construct(:i));
        IF ((:i >= : x)) THEN
        	EXIT simple_loop_when;
        END IF;
    END LOOP simple_loop_when;
END;
$$;

CREATE OR REPLACE PROCEDURE procedure11 (x INT)
RETURNS VARCHAR
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
    		DECLARE
    			i INTEGER := 0;
BEGIN
    			--** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
    LOOP
        i := i + 1;
		IF (:I = 5) THEN
        	CONTINUE;
		END IF;
        CALL RAISE_MESSAGE_UDF('INFO', 'i %', array_construct(:i));
        IF ((:i >= : x)) THEN
        	EXIT;
        END IF;
    END LOOP;
END;
$$;