CREATE PROCEDURE

Translation reference to convert Teradata CREATE PROCEDURE to Snowflake Scripting

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

The Teradata CREATE PROCEDURE and REPLACE PROCEDURE statement generates or replaces a stored procedure implementation and compiles it.

For more information about CREATE PROCEDURE or REPLACE PROCEDURE click here.

-- Create/replace procedure syntax
{CREATE | REPLACE} PROCEDURE [database_name. | user_name.] procedure_name
    ([<parameter_definition>[, ...n]])
[<SQL_data_access>]
[DYNAMIC RESULT SETS number_of_sets]
[SQL SECURITY <privilege_option>]
statement;


<parameter_definition> := [IN | OUT | INOUT] parameter_name data_type 

<SQL_data_access> := {CONTAINS SQL | MODIFIES SQL DATA | READS SQL DATA}

<privilege_option> := {CREATOR | DEFINER | INVOKER | OWNER}

Sample Source Patterns

Setup data

The following code is necessary to execute the sample patterns present in this section.

Teradata

IN -> Teradata_01.sql

CREATE TABLE inventory (
    product_name VARCHAR(50),
    price INTEGER
);

INSERT INTO inventory VALUES ('Bread', 50);
INSERT INTO inventory VALUES ('Tuna', 150);
INSERT INTO inventory VALUES ('Gum', 20);
INSERT INTO inventory VALUES ('Milk', 80);

Snowflake

Basic Procedure

Teradata

Query

IN -> Teradata_02.sql

REPLACE PROCEDURE BasicProcedure(IN counterValue INTEGER)
BEGIN
    DECLARE productName VARCHAR(50);
    DECLARE productPrice INTEGER DEFAULT 0;
    DECLARE whileCounter INTEGER DEFAULT 0;
    SET productName = 'Salt';
    WHILE (whileCounter < counterValue) DO
        SET productPrice = 10 + productPrice;
        SET whileCounter = whileCounter + 1;
    END WHILE;
    INSERT INTO inventory VALUES (productName, productPrice);
END;

CALL BasicProcedure(5);
SELECT product_name, price FROM inventory WHERE product_name = 'Salt';

Result

Snowflake Scripting

Query

OUT -> Teradata_02.sql

CREATE OR REPLACE PROCEDURE BasicProcedure (COUNTERVALUE INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/10/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        productName VARCHAR(50);
        productPrice INTEGER DEFAULT 0;
        whileCounter INTEGER DEFAULT 0;
    BEGIN
         
         
         
        productName := 'Salt';
            WHILE (:whileCounter < :counterValue) LOOP
            productPrice := 10 + productPrice;
            whileCounter := whileCounter + 1;
        END LOOP;
        INSERT INTO inventory
        VALUES (:productName, :productPrice);
    END;
$$;

CALL BasicProcedure(5);

SELECT
    product_name,
    price FROM
    inventory
WHERE
    UPPER(RTRIM( product_name)) = UPPER(RTRIM('Salt'));

Result

Single out parameter

Teradata

Query

IN -> Teradata_03.sql

REPLACE PROCEDURE procedureLabelSingle(OUT Message VARCHAR(100))
BEGIN
    set Message = 'Assignment value. Thanks';
END;

CALL procedureLabelSingle(?);

Result

Snowflake Scripting

Query

OUT -> Teradata_03.sql

CREATE OR REPLACE PROCEDURE procedureLabelSingle (MESSAGE OUT VARCHAR(100))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/23/2024" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        Message := 'Assignment value. Thanks';
    END;
$$;

CALL procedureLabelSingle(?);

Result

Multiple out parameter

Teradata

Query

IN -> Teradata_04.sql

REPLACE PROCEDURE procedureLabelMultiple(OUT Message VARCHAR(100), OUT Message2 VARCHAR(100))
BEGIN
    set Message = 'Assignment value. Thanks';
    set Message2 = 'Assignment value2. Thanks';
END;

CALL procedureLabelSingle(?, ?);

Result

Snowflake Scripting

Query

OUT -> Teradata_04.sql

CREATE OR REPLACE PROCEDURE procedureLabelMultiple (MESSAGE OUT VARCHAR(100), MESSAGE2 OUT VARCHAR(100))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        Message := 'Assignment value. Thanks';
        Message2 := 'Assignment value2. Thanks';
    END;
$$;

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "procedureLabelSingle" **
CALL procedureLabelSingle(?, ?);

Result

Multiple out parameter with dynamic result sets

Teradata

Query

IN -> Teradata_05.sql

REPLACE PROCEDURE Procedure1(out product_name VARCHAR(50), out price integer)
DYNAMIC RESULT SETS 2
BEGIN
	DECLARE result_set CURSOR WITH RETURN ONLY FOR
	SELECT * FROM inventory;
    DECLARE result_set2 CURSOR WITH RETURN ONLY FOR
	SELECT * FROM inventory;
    SET price = 100;
    SET product_name = 'another2';
	OPEN result_set2;
	OPEN result_set;
END;

REPLACE PROCEDURE Procedure2()
BEGIN
 DECLARE price INTEGER;
 DECLARE productName varchar(10);
 CALL Procedure1(productName, price);
 INSERT INTO inventory VALUES(:productName, :price);
END;

CALL Procedure2();

Result

Snowflake Scripting

Query

OUT -> Teradata_05.sql

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "inventory" **
CREATE OR REPLACE PROCEDURE Procedure1 (PRODUCT_NAME OUT VARCHAR(50), PRICE OUT integer)
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		tbl_result_set VARCHAR;
		tbl_result_set2 VARCHAR;
		return_arr ARRAY := array_construct();
	BEGIN
		tbl_result_set := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
		CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_result_set) AS
			SELECT
				* FROM
				inventory;
		LET result_set CURSOR
		FOR
			SELECT
				*
			FROM
				IDENTIFIER(?);
		tbl_result_set2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
		CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_result_set2) AS
			SELECT
				* FROM
				inventory;
		LET result_set2 CURSOR
		FOR
			SELECT
				*
			FROM
				IDENTIFIER(?);
				price := 100;
				product_name := 'another2';
				OPEN result_set2 USING (tbl_result_set2);
				return_arr := array_append(return_arr, :tbl_result_set2);
				OPEN result_set USING (tbl_result_set);
				return_arr := array_append(return_arr, :tbl_result_set);
				--** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
				RETURN return_arr;
	END;
$$;

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "inventory" **
CREATE OR REPLACE PROCEDURE Procedure2 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
	DECLARE
				price INTEGER;
				productName varchar(10);
	BEGIN
				 
				 
				CALL Procedure1(:productName, :price);
				INSERT INTO inventory
				VALUES (:productName, :price);
	END;
$$;

CALL Procedure2();

Result

Known Issues

1. SQL Data Access

By default, Snowflake procedures support the execution of any kind of SQL statements, including data reading or modification statements, making the SQL data access clause non-relevant. This clause will be ignored when converting the procedure.

2. Top Level Objects in Assessment Report

Elements (Temporal tables or Views) inside Stored Procedures are being counted in the Assessment report as Top Level Objects. The SnowConvert team is now working on a fix for this scenario.

Related EWIs

1. SSC-EWI-0073: Pending Functional Equivalence Review.

2. SSC-FDM-0020: Multiple result sets are returned in temporary tables.