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