If the stored procedure’s signature omits the argument name, you can declare an alias for the argument.
There is no support for this in Snowflake.
To achieve functional equivalence, aliases will be removed, and all usages will be renamed.
When an alias is declared for a parameter nameless, a generated name will be created for the parameter and the usages. When the alias is for a parameter with name the alias will be replaced by the real parameter name.
CREATEORREPLACEPROCEDURE 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 parameterCREATEORREPLACEPROCEDURE 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;
$$;
Amazon Redshift stored procedures support parameters that can be passed during procedure invocation. These parameters allow you to provide input values, retrieve output values, or use them for input and output operations. Below is a detailed explanation of the types of parameters, their modes, and examples of their usage. Snowflake only supports input values.
Like Redshift, Snowflake supports CREATE PROCEDURE using ` procedure_logic ` as the body. There is a difference in the Redshift syntax where a word can be inside the ` like $word$ and used as a delimiter body like $word$ procedure_logic $word$. SnowConvert AI will transform it by removing the word, leaving the `.
PL/pgSQL is a block-structured language. The complete body of a procedure is defined in a block, which contains variable declarations and PL/pgSQL statements. A statement can also be a nested block, or subblock.
Section to declare all the procedure variables except for loop variables.
Redshift supports multiple DECLARE sections per block statement, since Snowflake does not support this behavior they must be merged into a single declaration statement per block.
When an exception occurs, and you add an exception-handling block, you can write RAISE statements and most other PL/pgSQL statements. For example, you can raise an exception with a custom message or insert a record into a logging table.
CREATEORREPLACEPROCEDURE update_employee_sp()AS$$
BEGIN
select var;
EXCEPTION WHEN OTHERS THEN
RAISE INFO 'An exception occurred.';
END;
$$LANGUAGE plpgsql;
The NONATOMIC commits after each statement in the stored procedure. Snowflake supports an AUTOCOMMIT parameter. The default setting for AUTOCOMMIT is TRUE (enabled).
While AUTOCOMMIT is enabled, Each statement outside an explicit transaction is treated as inside its implicit single-statement transaction. In other words, that statement is automatically committed if it succeeds and automatically rolled back if it fails. In other words, Snowflake works as NONATOMIC “by default”.
Redshift supports nameless parameters by referencing the parameters by their position using $. Snowflake does not support this behavior. To ensure functional equivalence, SnowConvert AI can convert those references by the parameter’s name if the name is present in the definition. If not, SnowConvert AI will generate a name for the parameter, and the uses will be replaced with the new name.
In Amazon Redshift, the RAISE statement is used to generate messages in the console or throw custom exceptions. Redshift allows you to specify different levels to indicate the severity of the message. In Snowflake, this functionality can be emulated using a user-defined function (UDF) that makes a call to the console depending on the specified level.
Exception:
When the level is “EXCEPTION”, a custom exception is raised with a general message: “To view the EXCEPTION MESSAGE, you need to check the log.” The exception code is -20002, which informs the user that the custom message can be found in the logs. This is due to limitations when sending custom exceptions in Snowflake.
Warning:
If the level is “WARNING”, SYSTEM$LOG_WARN is used to print the warning message to Snowflake’s log, which helps highlight potential issues without interrupting the flow of execution.
Info:
For any other level (such as “INFO”), SYSTEM$LOG_INFO is used to print the message to the console log, providing more detailed feedback about the system’s state without causing critical disruptions.
This approach allows emulating Redshift’s severity levels functionality, adapting them to Snowflake’s syntax and features, while maintaining flexibility and control over the messages and exceptions generated during execution.
Limitations
To view logs in Snowflake, it is necessary to have specific privileges, such as the ACCOUNTADMIN or SECURITYADMIN roles.
Logs in Snowflake are not available immediately and may have a slight delay before the information is visible.
Personalized error messages in exceptions are not displayed like in Redshift. To view custom messages, you must access the logs directly.
For further information, please refer to the following page.
The conversion of the return statement from Amazon Redshift to Snowflake is straightforward, only considering adding a NULL to the return statement on Snowflake.
SnowConvert AI returns a variant with parameters set up as output parameters. So, for each return, SnowConvert AI will add a variant as a return value.
The SECURITY clause in Amazon Redshift stored procedures defines the access control and permissions context under which the procedure executes. This determines whether the procedure uses the privileges of the owner (creator) or the caller (user invoking the procedure).
In Redshift, the CONSTANT keyword prevents variable reassignment during execution. Since Snowflake does not support this keyword, it is removed during transformation. This does not impact functionality, as the logic should not attempt to reassign a constant variable.
The NOT NULL constraint in Redshift ensures a variable cannot be assigned a null value and requires a non-null default value. As Snowflake does not support this constraint, it is removed during transformation. However, the default value is retained to maintain functionality.
A variable declare with a Refcursor is transformed to Resultset type, for more information.
Commits the current transaction to the database. This command makes the database updates from the transaction permanent. (Redshift SQL Language Reference COMMIT)
COMMIT in a default transaction behavior procedure (without NONATOMIC clause)¶
To avoid out of scope transaction exceptions in Snowflake, the usages of COMMIT will be matched with BEGIN TRANSACTION.
When multiple COMMIT statements are present in the procedure, multiple BEGIN TRANSACTION statements will be generated after every COMMIT to emulate the Redshift transaction behavior.
CREATEORREPLACEPROCEDURE 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;
CREATEORREPLACEPROCEDURE transaction_test (a INT)RETURNSVARCHARLANGUAGESQLAS$$
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;
CREATEORREPLACEPROCEDURE 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;
In Redshift, when a COMMIT statement is specified in a nested procedure call, the command will commit all pending work from previous statements in the current and parent scopes. Committing the parent scope actions is not supported in Snowflake, when this case is detected an FDM will be generated.
CREATEORREPLACEPROCEDURE transaction_test(a INT)LANGUAGE plpgsql
AS$$
BEGIN
INSERT INTO transaction_values_test VALUES (a);
COMMIT;
END
$$;CREATEORREPLACEPROCEDURE 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
$$;
ROLLBACK in a default transaction behavior procedure (without NONATOMIC clause)¶
To avoid out of scope transaction exceptions in Snowflake, the usages of ROLLBACK will be matched with BEGIN TRANSACTION.
When multiple transaction control statements are present in the procedure, multiple BEGIN TRANSACTION statements will be generated after every each one of them to emulate the Redshift transaction behavior.
CREATEORREPLACEPROCEDURE transaction_test (a INT)RETURNSVARCHARLANGUAGESQLAS$$
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;
CREATEORREPLACEPROCEDURE 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;
In Redshift, when a ROLLBACK statement is specified in a nested procedure call, the command will commit all pending work from previous statements in the current and parent scopes. Committing the parent scope actions is not supported in Snowflake, when this case is detected an FDM will be generated.
CREATEORREPLACEPROCEDURE 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
$$;CREATEORREPLACEPROCEDURE nested_transaction_test(a int)LANGUAGE plpgsql
AS$$
BEGIN
INSERT INTO transaction_values_test values (a);
CALL transaction_test(a + 3);
COMMIT;
END
$$;
CREATEORREPLACEPROCEDURE transaction_test (a int)RETURNSVARCHARLANGUAGESQLCOMMENT='{ "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
$$;CREATEORREPLACEPROCEDURE nested_transaction_test (a int)RETURNSVARCHARLANGUAGESQLCOMMENT='{ "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
$$;
2. ROLLBACK of DDL statements
In Snowflake, DDL statements perform an implicit commit whenever they are executed inside a procedure, making effective all the work before executing the DDL as well as the DDL itself. This causes the ROLLBACK statement to not be able to discard any changes before that point, this issue will be informed using an FDM.
TRUNCATE in a default transaction behavior procedure (without NONATOMIC clause)¶
Since the TRUNCATE statement automatically commits the transaction it is executed in, any of its usages will generate a COMMIT statement in Snowflake to emulate this behavior.
Since a COMMIT statement is generated the same BEGIN TRANSACTION statement generation will be applied to TRUNCATE. For more information check the COMMIT translation specification.
CREATEORREPLACEPROCEDURE truncate_in_procedure (a int)RETURNSVARCHARLANGUAGESQLAS$$
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;
The NONATOMIC behavior from Redshift is emulated in Snowflake by using the session parameter AUTOCOMMIT set to true.
Since the AUTOCOMMIT session parameter is assumed to be true by SnowConvert AI, the TRUNCATE statement inside NONATOMIC procedures is left as is, there is no need to generate a COMMIT statement because every statement is automatically committed when executed.
CREATEORREPLACEPROCEDURE 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;
In Redshift, when a COMMIT statement is specified in a nested procedure call, the command will commit all pending work from previous statements in the current and parent scopes. Committing the parent scope actions is not supported in Snowflake, when this case is detected an FDM will be generated.
CREATEORREPLACEPROCEDURE transaction_test(a INT)LANGUAGE plpgsql
AS$$
BEGIN
INSERT INTO transaction_values_test VALUES (a);
TRUNCATE TABLE transaction_values_test;
END
$$;CREATEORREPLACEPROCEDURE 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
$$;
The CASE statement in Redshift lets you return values based on conditions, enabling conditional logic in queries. It has two forms: simple and searched. (Redshift SQL Language Reference Conditionals: Case).
CREATEPROCEDURE 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;
$$;
CREATEPROCEDURE PROC1 (paramNumber int)RETURNSVARCHARLANGUAGESQLCOMMENT='{ "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;
$$;
In Redshift, when a CASE expression is executed and none of the validated conditions are met, and there is no ELSE defined, the exception ‘CASE NOT FOUND’ is triggered. In Snowflake, the code executes but returns no result. To maintain the same functionality in Snowflake in this scenario, an exception with the same name will be declared and executed if none of the CASE conditions are met.
Note
:class: tip
Case Without Else are fully supported by Snowflake.
SnowConvert AI will add the parenthesis in the conditions and change the keyword ELSIF by ELSEIF since Redshift does not require the parenthesis in the conditions and ELSIF is the keyword.
CREATEPROCEDURE 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;
$$;
CREATEORREPLACEPROCEDURE 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;
$$;CREATEORREPLACEPROCEDURE 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;
$$;
CREATEORREPLACEPROCEDURE simple_loop_when(x int)LANGUAGE plpgsql
AS$$
DECLARE i INTEGER := 0;
BEGIN
<<simple_loop_when>>
LOOP
RAISE INFO 'i %', i;
i := i + 1;
EXIT simple_loop_when WHEN (i >= x);
END LOOP;
END;
$$;
CREATEORREPLACEPROCEDURE procedure1 ()AS$$
BEGIN
FOR i IN 1..10 LOOP
NULL;
END LOOP;
FOR i IN REVERSE 10..1 LOOP
NULL;
END LOOP;
END;
$$LANGUAGE plpgsql;
CREATEORREPLACEPROCEDURE simple_loop()LANGUAGE plpgsql
AS$$
BEGIN
<<simple_while>>
LOOP
RAISE INFO 'I am raised once';
EXIT simple_while;
RAISE INFO 'I am not raised';
END LOOP;
RAISE INFO 'I am raised once as well';
END;
$$;
1. Fetch without target variables is not supported
Snowflake requires the FETCH statement to specify the INTO clause with the variables where the fetched row values are going to be stored. When a FETCH statement is found in the code with no INTO clause an EWI will be generated.
Input Code:
FETCH FORWARD FROM cursor1;
Output Code:
!!!RESOLVE EWI!!!/*** SSC-EWI-PG0015 - FETCH CURSOR WITHOUT TARGET VARIABLES IS NOT SUPPORTED IN SNOWFLAKE ***/!!!FETCH FORWARD FROM cursor1;
Cursor arguments have to be bound per each one of its uses, SnowConvert AI will generate the bindings, as well as reorder and repeat the passed values to the OPEN statement as needed to satisfy the bindings.
Open cursor with procedure parameters or local variables¶
The procedure parameters or local variables have to be bound per each one of its uses in the cursor query, SnowConvert AI will generate the bindings and add the parameter or variable names to the OPEN statement, even if the cursor originally had no parameters.
Since Snowflake does not support the REFCURSOR data type, its functionality is replicated by converting the REFCURSOR variable into a RESULTSET type. The query used to open the REFCURSOR is assigned to the RESULTSET variable, after which a new cursor is created and linked to the RESULTSET variable. Additionally, all references to the original REFCURSOR within the cursor logic are updated to use the new cursor, thereby replicating the original functionality.
CREATEORREPLACEPROCEDURE VARIABLE_REFCURSOR()LANGUAGE plpgsql
AS$$
DECLARE
v_curs1 refcursor;
BEGIN
OPEN v_curs1 FOR SELECT column1_name, column2_name FROM your_table;
-- Cursor logic
CLOSE v_curs1;
END;
$$;
CREATEORREPLACEPROCEDURE VARIABLE_REFCURSOR_DYNAMIC(min_salary NUMERIC)LANGUAGE plpgsql
AS$$
DECLARE
cur refcursor;
qry TEXT;
BEGIN
qry := 'SELECT id, name FROM employees WHERE salary > ' || min_salary;
OPEN cur FOR EXECUTE qry;
-- Cursor logic
CLOSE cur;
END;
$$;CREATEORREPLACEPROCEDURE VARIABLE_REFCURSOR_DYNAMIC2(min_salary NUMERIC)LANGUAGE plpgsql
AS$$
DECLARE
cur refcursor;
BEGIN
OPEN cur FOR EXECUTE 'SELECT id, name FROM employees WHERE salary > ' || min_salary;
-- Cursor logic
CLOSE cur;
END;
$$;
CREATEORREPLACEPROCEDURE VARIABLE_REFCURSOR()LANGUAGE plpgsql
AS$$
DECLARE
v_curs1 refcursor;
BEGIN
OPEN v_curs1 FOR SELECT column1_name, column2_name FROM your_table;
-- Cursor logic
CLOSE v_curs1;
OPEN v_curs1 FOR SELECT column3_name, column4_name FROM your_table2;
-- Cursor logic
CLOSE v_curs1;
END;
$$;