Snowflake Documentation Home

CREATE PROCEDURE

Translation reference to convert Teradata CREATE PROCEDURE to Snowflake Scripting

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.

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);

Basic Procedure

Teradata

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';

Snowflake Scripting

OUT -> Teradata_02.sql
CREATE OR REPLACE PROCEDURE BasicProcedure (COUNTERVALUE FLOAT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET productName VARCHAR(50);
        LET productPrice INTEGER DEFAULT 0;
        LET whileCounter INTEGER DEFAULT 0;
        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 product_name = 'Salt';

Single out parameter

Teradata

IN -> Teradata_03.sql
REPLACE PROCEDURE procedureLabelSingle(OUT Message VARCHAR(100))
BEGIN
    set Message = 'Assignment value. Thanks';
END;

CALL procedureLabelSingle(?);

Snowflake Scripting

OUT -> Teradata_03.sql
CREATE OR REPLACE PROCEDURE procedureLabelSingle (
--OUT 
MESSAGE STRING)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        Message := 'Assignment value. Thanks';
        RETURN Message;
    END;
$$;

CALL procedureLabelSingle(?);

Multiple out parameter

Teradata

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(?, ?);

Snowflake Scripting

OUT -> Teradata_04.sql
CREATE OR REPLACE PROCEDURE procedureLabelMultiple (
--OUT
MESSAGE STRING,
--OUT
MESSAGE2 STRING)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
    Message := 'Assignment value. Thanks';
    Message2 := 'Assignment value2. Thanks';
    RETURN OBJECT_CONSTRUCT('Message', :Message, 'Message2', :Message2);
    END;
$$;

CALL procedureLabelSingle(?, ?);

Inside a procedure, the returned object can be easily deconstructed by using the following statements right after performing the procedure call: LET call_results VARIANT := (SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())));

var1 := GET(:call_results, 'Message');

var2 := GET(:call_results, 'Message2'); \

Multiple out parameter with dynamic result sets

Teradata

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();

Snowflake Scripting

OUT -> Teradata_05.sql
CREATE OR REPLACE PROCEDURE Procedure1 (out !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'OUTPUT PARAMETERS ALONG WITH DYNAMIC RESULT SET' NODE ***/!!! PRODUCT_NAME STRING, out !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'OUTPUT PARAMETERS ALONG WITH DYNAMIC RESULT SET' NODE ***/!!! PRICE FLOAT)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
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 OBJECT_CONSTRUCT('SC_RET_VALUE', :return_arr, 'product_name', :product_name, 'price', :price);
	END;
$$;

CREATE OR REPLACE PROCEDURE Procedure2 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
				call_results VARIANT;
	BEGIN
				LET price INTEGER;
				LET productName varchar(10);
				call_results := (
			CALL Procedure1(:productName, :price)
				);
				productName := :call_results:product_name;
				price := :call_results:price;
				INSERT INTO inventory
				VALUES (:productName, :price);
	END;
$$;

CALL Procedure2();

Known Issues

1. Out Parameters

Snowflake Scripting does not support OUT/INOUT parameters therefore these parameters are returned at the end of the procedure's body.

2. 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.

3. 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.

PreviousCREATE MACRONextCURSOR

Last updated