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.

Teradata

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

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

CREATE OR REPLACE PROCEDURE PUBLIC.BasicProcedure (COUNTERVALUE FLOAT)
RETURNS VARCHAR
LANGUAGE SQL
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 PUBLIC.inventory VALUES (:productName, :productPrice);
   END;
$$;

CALL BasicProcedure(5);

SELECT
product_name,
price
FROM PUBLIC.inventory WHERE product_name = 'Salt';

Result

Single out parameter

Teradata

Query

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

CALL procedureLabelSingle(?);

Result

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.procedureLabelSingle (
/*** MSC-ERROR - MSCEWI1078 - OUTPUT PARAMETERS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
MESSAGE STRING)
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
            Message := 'Assignment value. Thanks';
            RETURN Message;
    END;
$$;

CALL procedureLabelSingle('');

Result

Multiple out parameter

Teradata

Query

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

CREATE OR REPLACE PROCEDURE shema1.PUBLIC.procedureLabelMultiple (
/*** MSC-ERROR - MSCEWI1078 - OUTPUT PARAMETERS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
MESSAGE STRING,
/*** MSC-ERROR - MSCEWI1078 - OUTPUT PARAMETERS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
MESSAGE2 STRING)
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
            Message := 'Assignment value. Thanks';
            Message2 := 'Assignment value2. Thanks';
            RETURN OBJECT_CONSTRUCT('Message', :Message, 'Message2', :Message2);
    END;
$$;

CALL procedureLabelSingle('','');

Result

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

Query

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

CREATE OR REPLACE PROCEDURE Procedure1 (
--out /*** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR OUTPUT PARAMETERS ALONG WITH DYNAMIC RESULT SET IS PLANNED TO BE DELIVERED IN THE FUTURE ***/
PRODUCT_NAME STRING,
--out /*** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR OUTPUT PARAMETERS ALONG WITH DYNAMIC RESULT SET IS PLANNED TO BE DELIVERED IN THE FUTURE ***/
PRICE FLOAT)
RETURNS VARIANT
LANGUAGE SQL
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);
  --** MSC-WARNING - MSCEWI1100 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
  --** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING **
  RETURN OBJECT_CONSTRUCT('SC_RET_VALUE', :return_arr, 'product_name', :product_name, 'price', :price);
END;
$$;

CREATE OR REPLACE PROCEDURE Procedure2 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
  call_results VARIANT;
BEGIN
  LET price INTEGER;
  LET productName varchar(10);
  call_results := (
  CALL Procedure1(:productName, :price)
  );
  --** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING **
  productName := :call_results:product_name;
  price := :call_results:price;
  INSERT INTO inventory VALUES (:productName, :price);
END;
$$;

CALL Procedure2();

Result

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. MSCEWI1058: Snowscript functionality is not supported

2. MSCEWI1081: Snowflake Scripting procedures cannot return more than one result set

3. MSCEWI1082: Auto-Generated code in order to support output parameters in Snowflake Scripting

4. MSCEWI1100: Multiple result sets are returned in temporary tables

5. MSCEWI1073: Pending Functional Equivalence Review.