SnowConvert: Teradata SQL to Snowflake Scripting (Procedures)

ABORT and ROLLBACK

Description

Teradata’s ABORT and ROLLBACK statements are replaced by a ROLLBACK statement in Snowflake Scripting.

For more information on Teradata ABORT and for ROLLBACK.

 ABORT [abort_message] [FROM option] [WHERE abort_condition];

ROLLBACK [WORK] [abort_message] [FROM clause] [WHERE clause];
Copy

Sample Source Patterns

Basic ABORT and ROLLBACK

Teradata
 REPLACE PROCEDURE procedureBasicAbort() 
BEGIN
    ABORT;	
    ROLLBACK;
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE procedureBasicAbort ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        ROLLBACK;
        ROLLBACK;
    END;
$$;
Copy

Conditional ABORT and ROLLBACK

Teradata
 REPLACE PROCEDURE procedureWhereAbort(AnotherValueProc INTEGER) 
BEGIN
    ABORT WHERE AValueProc > 2;
	
    ROLLBACK WHERE (AnotherValueProc > 2);
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE procedureWhereAbort (ANOTHERVALUEPROC INTEGER)
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
        IF (AValueProc > 2) THEN
            ROLLBACK;
        END IF;
        IF (AnotherValueProc > 2) THEN
            ROLLBACK;
        END IF;
    END;
$$;
Copy

ABORT and ROLLBACK with table references and FROM clause

Teradata
 CREATE TABLE  ReferenceTable
    (ColumnValue INTEGER);
  
CREATE TABLE  ReferenceTable2
    (ColumnValue INTEGER);

REPLACE PROCEDURE procedureFromAbort() 
BEGIN
    ROLLBACK FROM ReferenceTable, ReferenceTable2
	WHERE ReferenceTable.ColumnValue = ReferenceTable2.ColumnValue;
    ABORT FROM ReferenceTable, ReferenceTable2
        WHERE ReferenceTable.ColumnValue = ReferenceTable2.ColumnValue;
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE TABLE ReferenceTable
(
	ColumnValue INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

CREATE TABLE ReferenceTable2
(
	ColumnValue INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

CREATE OR REPLACE PROCEDURE procedureFromAbort ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
	BEGIN
		LET _ROW_COUNT FLOAT;
		SELECT
			COUNT(*)
		INTO
			_ROW_COUNT
			FROM
			ReferenceTable,
			ReferenceTable2
				WHERE
			ReferenceTable.ColumnValue = ReferenceTable2.ColumnValue;
			IF (_ROW_COUNT > 0) THEN
			ROLLBACK;
			END IF;
			SELECT
			COUNT(*)
			INTO
			_ROW_COUNT
			FROM
			ReferenceTable,
			ReferenceTable2
			        WHERE
			ReferenceTable.ColumnValue = ReferenceTable2.ColumnValue;
			IF (_ROW_COUNT > 0) THEN
			ROLLBACK;
			END IF;
	END;
$$;
Copy

ABORT and ROLLBACK with table references without FROM clause

Teradata
 CREATE TABLE  ReferenceTable
    (ColumnValue INTEGER);
    
REPLACE PROCEDURE procedureFromTableAbort() 
BEGIN
    ROLLBACK WHERE ReferenceTable.ColumnValue > 2;
    ABORT WHERE ReferenceTable.ColumnValue > 4;
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE TABLE ReferenceTable
(
    ColumnValue INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

CREATE OR REPLACE PROCEDURE procedureFromTableAbort ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET _ROW_COUNT FLOAT;
        SELECT
            COUNT(*)
        INTO
            _ROW_COUNT
        FROM
            ReferenceTable
            WHERE
            ReferenceTable.ColumnValue > 2;
            IF (_ROW_COUNT > 0) THEN
            ROLLBACK;
            END IF;
            SELECT
            COUNT(*)
            INTO
            _ROW_COUNT
            FROM
            ReferenceTable
            WHERE
            ReferenceTable.ColumnValue > 4;
            IF (_ROW_COUNT > 0) THEN
            ROLLBACK;
            END IF;
    END;
$$;
Copy

Known Issues

1. Custom Error Message

Even though the ROLLBACK AND ABORT are supported, using them with a custom error message is not supported.

Teradata
 ABORT 'Error message for abort';
ROLLBACK  'Error message for rollback';
Copy
Snowflake Scripting
 ABORT 'Error message for abort';
ROLLBACK  'Error message for rollback';
Copy
2. Aggregate function

The use of the aggregate function combined with ABORT/ROLLBACK is not supported

Teradata
 ROLLBACK WHERE SUM(ATable.AValue) < 2;
ABORT WHERE SUM(ATable.AValue) < 2;
Copy
Snowflake Scripting
 ROLLBACK WHERE SUM(ATable.AValue) < 2;
ABORT WHERE SUM(ATable.AValue) < 2;
Copy

ACTIVITY_COUNT

Description

The ACTIVITY_COUNT status variable returns the number of rows affected by an SQL DML statement in an embedded SQL or stored procedure application. For more information check here.

There is no direct equivalent in Snowflake. However, there is a workaround to emulate the ACTIVITY_COUNT’s behavior. One must simply use the following query:

 SELECT $1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Copy

This query retrieves and returns the first column of the result set from the last executed query in the current session. Furthermore, $1 can be replaced by "number of rows inserted", "number of rows updated" or "number of rows deleted" based on the query type.

As expected, this translation behaves like its Teradata counterpart only when no other queries besides the SQL DML statement are executed before calling LAST_QUERY_ID.

Sample Source Patterns

Setup data

Teradata
 CREATE TABLE employees (
    employee_id INT NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10,2),
    PRIMARY KEY (employee_id)
);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
VALUES (1, 'John', 'Doe', 10, 60000.00);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
VALUES (2, 'Johny', 'Doey', 10, 65000.00);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
VALUES (3, 'Max', 'Smith', 10, 70000.00);

DROP TABLE activity_log;
CREATE TABLE activity_log (
    log_id INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
    operation VARCHAR(200),
    row_count INT,
    log_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (log_id)
);
Copy
Snowflake
 CREATE OR REPLACE TABLE employees (
    employee_id INT NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10,2),
    PRIMARY KEY (employee_id)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/11/2024" }}'
;

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
VALUES (1, 'John', 'Doe', 10, 60000.00);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
VALUES (2, 'Johny', 'Doey', 10, 65000.00);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
VALUES (3, 'Max', 'Smith', 10, 70000.00);

CREATE OR REPLACE TABLE activity_log (
    log_id INT DEFAULT activity_log_log_id.NEXTVAL NOT NULL,
    operation VARCHAR(200),
    row_count INT,
    log_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
    PRIMARY KEY (log_id)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/11/2024" }}'
;
Copy

Supported usage

Teradata
 REPLACE PROCEDURE UpdateEmployeeSalaryAndLog ()
BEGIN
    DECLARE row_count1 INT;

    UPDATE employees
    SET salary = 80000
    WHERE department_id = 10;

    -- Get the ACTIVITY_COUNT
    SET row_count1 = ACTIVITY_COUNT;

    -- Insert the ACTIVITY_COUNT into the activity_log table
    INSERT INTO activity_log (operation, row_count)
    VALUES ('UPDATE WHERE dept=10', row_count1);
END;

CALL UpdateEmployeeSalaryAndLog();

SELECT * FROM ACTIVITY_LOG;
Copy
LOG_ID | OPERATION    	      | ROW_COUNT | LOG_TIMESTAMP              |
-------+----------------------+-----------+----------------------------+
1      | UPDATE WHERE dept=10 |	3         | 2024-07-10 15:58:46.490000 |

Copy
Snowflake
 CREATE OR REPLACE PROCEDURE UpdateEmployeeSalaryAndLog ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/11/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        row_count1 INT;
    BEGIN
         
        UPDATE employees
    SET salary = 80000
    WHERE department_id = 10;

    -- Get the ACTIVITY_COUNT
        row_count1 := (
    SELECT
        $1
    FROM
        TABLE(RESULT_SCAN(LAST_QUERY_ID()))
        ) /*** SSC-FDM-TD0033 - 'ACTIVITY_COUNT' TRANSFORMATION MIGHT REQUIRE MANUAL ADJUSTMENTS ***/;

        -- Insert the ACTIVITY_COUNT into the activity_log table
        INSERT INTO activity_log (operation, row_count)
        VALUES ('UPDATE WHERE dept=10', :row_count1);
    END;
$$;

CALL UpdateEmployeeSalaryAndLog();

SELECT
    * FROM
    ACTIVITY_LOG;
Copy
LOG_ID | OPERATION    	      | ROW_COUNT | LOG_TIMESTAMP            |
-------+----------------------+-----------+--------------------------+
102    | UPDATE WHERE dept=10 |	3         | 2024-07-11T12:42:35.280Z |

Copy

Known Issues

  1. If ACTIVITY_COUNT is called twice or more times before executing a DML statement, the transformation might not return the expected values. Check here.

  2. If ACTIVITY_COUNT is called after a non DML statement was executed, the transformation will not return the expected values. Check here.

  3. ACTIVITY_COUNT requires manual fixing when inside a SELECT/SET INTO VARIABLE statement and was not able to be identified as a column name. Check here.

Related EWIs

  1. SSC-FDM-TD0033: ‘ACTIVITY_COUNT’ TRANSFORMATION MIGHT REQUIRE MANUAL ADJUSTMENTS.

BEGIN END

BEGIN END TRANSACTION

Description

Defines the beginning of an explicit logical transaction in Teradata session mode.

For more information regarding Teradata BEGIN END Transaction, check here.

 [ BEGIN TRANSACTION | BT ]
     statement
     [ statement ]... ]
[ END TRANSACTION | ET ];
Copy

Sample Source Pattern

Teradata
 REPLACE PROCEDURE BeginEndProcedure()
BEGIN
    DECLARE HELLOSTRING VARCHAR(60);
    BEGIN TRANSACTION
        SET HELLOSTRING = 'HELLO WORLD';
    END TRANSACTION;
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE BeginEndProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        HELLOSTRING VARCHAR(60);
    BEGIN
         
        BEGIN TRANSACTION;
        HELLOSTRING := 'HELLO WORLD';
        COMMIT;
    END;
$$;
Copy

BEGIN END REQUEST

Description

Delimits a SQL multistatement request

For more information regarding Teradata BEGIN END Request, check here.

 BEGIN REQUEST
     statement
     [ statement ]... ]
END REQUEST;
Copy

Sample Source Pattern

Teradata
 REPLACE PROCEDURE BeginEndProcedure()
BEGIN
    DECLARE HELLOSTRING VARCHAR(60);
    BEGIN REQUEST
        SET HELLOSTRING = 'HELLO WORLD';
    END REQUEST;
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE BeginEndProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        HELLOSTRING VARCHAR(60);
    BEGIN
         
        BEGIN
            HELLOSTRING := 'HELLO WORLD';
            COMMIT;
        EXCEPTION
            WHEN OTHER THEN
                ROLLBACK;
        END;
    END;
$$;
Copy

BEGIN END COMPOUND

Description

Delimits a compound statement in a stored procedure.

For more information regarding Teradata BEGIN END Compound, check here.

 label_name: BEGIN
     statement
     [ statement ]... ]
END label_name;
Copy

Sample Source Pattern

Teradata
 REPLACE PROCEDURE BeginEndProcedure()
BEGIN
    DECLARE HELLOSTRING VARCHAR(60);
    label_name: BEGIN
        SET HELLOSTRING = 'HELLO WORLD';
    END label_name;
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE BeginEndProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        HELLOSTRING VARCHAR(60);
    BEGIN
         
        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'label_name LABEL' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        label_name:
        BEGIN
            HELLOSTRING := 'HELLO WORLD';
        END;
    END;
$$;
Copy

Known Issues

1. Labels not supported in outer BEGIN END blocks

Teradata
 REPLACE PROCEDURE procedureLabelSingle()
label_name: BEGIN
    DECLARE HELLOSTRING VARCHAR(60);
    SET HELLOSTRING = 'HELLO WORLD';
END label_name;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE procedureLabelSingle ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'label_name LABEL' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
    label_name:
    DECLARE
        HELLOSTRING VARCHAR(60);
    BEGIN
         
        HELLOSTRING := 'HELLO WORLD';
    END;
$$;
Copy

Related EWIs

  1. SSC-EWI-0058: Functionality is not currently supported by Snowflake Scripting.

CASE

Description

Provides conditional execution of statements based on the evaluation of the specified conditional expression or equality of two operands.

The CASE statement is different from the SQL CASE expression_,_ which returns the result of an expression.

For more information regarding Teradata CASE, check here.

 -- Simple CASE
CASE operant_1
[ WHEN operant_2 THEN
     statement
     [ statement ]... ]...
[ ELSE   
     statement
     [ statement ]... ]
END CASE;

-- Searched CASE
CASE
[ WHEN conditional_expression THEN
     statement
     [ statement ]... ]...
[ ELSE   
     statement
     [ statement ]... ]
END CASE;
Copy

Sample Source Patterns

Sample auxiliar table

 CREATE TABLE case_table(col varchar(30));
Copy
 CREATE OR REPLACE TABLE case_table (
col varchar(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;
Copy

Simple Case

Teradata
 CREATE  PROCEDURE caseExample1 ( grade NUMBER )
BEGIN
    CASE grade
        WHEN 10 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Excellent');
        WHEN 9 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Very Good');
        WHEN 8 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Good');
        WHEN 7 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Fair');
        WHEN 6 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Poor');
        ELSE INSERT INTO CASE_TABLE(COL) VALUES ('No such grade');
    END CASE;
END;

CALL caseExample1(6);
CALL caseExample1(4);
CALL caseExample1(10);
SELECT * FROM CASE_TABLE;
Copy
|COL          |
|-------------|
|Poor         |
|No such grade|
|Excellent    |


Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE caseExample1 (GRADE NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CASE (grade)
            WHEN 10 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Excellent');
            WHEN 9 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Very Good');
            WHEN 8 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Good');
            WHEN 7 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Fair');
            WHEN 6 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Poor');
            ELSE
                INSERT INTO CASE_TABLE (COL)
                VALUES ('No such grade');
        END CASE;
    END;
$$;

CALL caseExample1(6);
CALL caseExample1(4);
CALL caseExample1(10);
SELECT * FROM CASE_TABLE;
Copy
|COL          |
|-------------|
|Poor         |
|No such grade|
|Excellent    |


Copy

Searched Case

Teradata
 CREATE PROCEDURE caseExample2 ( grade NUMBER )
BEGIN
    CASE
        WHEN grade = 10 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Excellent');
        WHEN grade = 9 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Very Good');
        WHEN grade = 8 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Good');
        WHEN grade = 7 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Fair');
        WHEN grade = 6 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Poor');
        ELSE INSERT INTO CASE_TABLE(COL) VALUES ('No such grade');
    END CASE;
END;

CALL caseExample2(6);
CALL caseExample2(4);
CALL caseExample2(10);
SELECT * FROM CASE_TABLE;
Copy
|COL          |
|-------------|
|Poor         |
|No such grade|
|Excellent    |

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE caseExample2 (GRADE NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CASE
            WHEN grade = 10 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Excellent');
            WHEN grade = 9 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Very Good');
            WHEN grade = 8 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Good');
            WHEN grade = 7 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Fair');
            WHEN grade = 6 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Poor');
            ELSE
                INSERT INTO CASE_TABLE (COL)
                VALUES ('No such grade');
        END CASE;
    END;
$$;

CALL caseExample2(6);
CALL caseExample2(4);
CALL caseExample2(10);

SELECT * FROM CASE_TABLE;
Copy
|COL          |
|-------------|
|Poor         |
|No such grade|
|Excellent    |

Copy

Known Issues

No issues were found.

Related EWIs

No related EWIs.

CREATE MACRO

Description

The Teradata CREATE MACRO defines one or more statements that are commonly used or that perform a complex operation, thus avoiding writing the same sequence of statements multiple times. The macro is executed when it is called by the EXECUTE statement.

For more information about CREATE MACRO click here.

 CREATE MACRO <macroname> [(parameter1, parameter2,...)] ( 
   <sql_statements> 
);

[ EXECUTE | EXEC ] <macroname>;
Copy

Sample Source Patterns

Setup data

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

 CREATE TABLE DEPOSIT
(
    ACCOUNTNO NUMBER,
    ACCOUNTNAME VARCHAR(100)
);

INSERT INTO DEPOSIT VALUES (1, 'Account 1');
INSERT INTO DEPOSIT VALUES (2, 'Account 2');
INSERT INTO DEPOSIT VALUES (3, 'Account 3');
INSERT INTO DEPOSIT VALUES (4, 'Account 4');
Copy
 CREATE OR REPLACE TABLE DEPOSIT
(
    ACCOUNTNO NUMBER(38, 18),
    ACCOUNTNAME VARCHAR(100)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

INSERT INTO DEPOSIT
VALUES (1, 'Account 1');

INSERT INTO DEPOSIT
VALUES (2, 'Account 2');

INSERT INTO DEPOSIT
VALUES (3, 'Account 3');

INSERT INTO DEPOSIT
VALUES (4, 'Account 4');
Copy

Basic Macro

Since there is no macro object in Snowflake, the conversion tool transforms Teradata macros into Snowflake Scripting stored procedures. Besides, to replicate the functionality of the returned result set, in Snowflake Scripting, the query that is supposed to return a data set from a macro is assigned to a RESULTSET variable which will then be returned.

Teradata
 REPLACE MACRO DEPOSITID (ID INT)
AS
(
  SELECT * FROM DEPOSIT WHERE ACCOUNTNO=:ID;
);

EXECUTE DEPOSITID(2);
Copy
+--------------+--------------+
| ACCOUNTNO    | ACCOUNTNAME  |
|--------------+--------------|
| 2            | Account 2    |
+--------------+--------------+

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE DEPOSITID (ID FLOAT)
RETURNS TABLE ()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET res RESULTSET := (SELECT * FROM DEPOSIT WHERE ACCOUNTNO=:ID);
        RETURN TABLE(res);
    END;
$$;

CALL DEPOSITID(2);
Copy
+--------------+--------------+
| ACCOUNTNO    | ACCOUNTNAME  |
|--------------+--------------|
| 2            | Account 2    |
+--------------+--------------+

Copy

Macro Calls Another Macro

SnowConvert supports the scenario where a macro calls another macro and, by transitivity, a result set is returned by getting the results from Snowflake’s RESULT_SCAN(LAST_QUERY_ID()).

Teradata
 REPLACE MACRO MacroCallOtherMacro (ID INT)
AS
(
    EXECUTE DEPOSITID(:ID);
);

EXECUTE MacroCallOtherMacro(2);
Copy
+--------------+--------------+
| ACCOUNTNO    | ACCOUNTNAME  |
|--------------+--------------|
| 2            | Account 2    |
+--------------+--------------+

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE MacroCallOtherMacro (ID FLOAT)
RETURNS TABLE (
)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "09/09/2024" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CALL DEPOSITID(:ID);
        LET res RESULTSET :=
        (
            SELECT
                *
            FROM
                TABLE(RESULT_SCAN(LAST_QUERY_ID()))
        );
        RETURN TABLE(res);
    END;
$$;

CALL MacroCallOtherMacro(2);
Copy
+--------------+--------------+
| ACCOUNTNO    | ACCOUNTNAME  |
|--------------+--------------|
| 2            | Account 2    |
+--------------+--------------+

Copy

Macro with no result set

Not all macros are intended to return a result set. The mentioned scenario is also supported.

Teradata
 REPLACE MACRO MacroWithoutSelect (ACCOUNTNO NUMBER, ACCOUNTNAME VARCHAR(100))
AS
(
  INSERT INTO DEPOSIT VALUES (:ACCOUNTNO, :ACCOUNTNAME);
);

EXECUTE MacroWithoutSelect(5, 'Account 5');
SELECT * FROM DEPOSIT;
Copy
+--------------+--------------+
| ACCOUNTNO    | ACCOUNTNAME  |
|--------------+--------------|
| 1            | Account 1    |
+--------------+--------------+
| 2            | Account 2    |
+--------------+--------------+
| 3            | Account 3    |
+--------------+--------------+
| 4            | Account 4    |
+--------------+--------------+
| 5            | Account 5    |
+--------------+--------------+

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE MacroWithoutSelect (ACCOUNTNO FLOAT, ACCOUNTNAME VARCHAR(100))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        INSERT INTO DEPOSIT
        VALUES (:ACCOUNTNO, :ACCOUNTNAME);
    END;
$$;

CALL MacroWithoutSelect(5, 'Account 5');
SELECT * FROM DEPOSIT;
Copy
+--------------+--------------+
| ACCOUNTNO    | ACCOUNTNAME  |
|--------------+--------------|
| 1            | Account 1    |
+--------------+--------------+
| 2            | Account 2    |
+--------------+--------------+
| 3            | Account 3    |
+--------------+--------------+
| 4            | Account 4    |
+--------------+--------------+
| 5            | Account 5    |
+--------------+--------------+

Copy

Macro returns multiple result sets

In Teradata, macros can return more than one result set from a single macro.

Snowflake Scripting procedures only allow one result set to be returned per procedure. To replicate Teradata behavior, when there are two or more result sets to return, they are stored in temporary tables. The Snowflake Scripting procedure will return an array containing the name of the temporary tables.

Teradata
 REPLACE MACRO DEPOSITID (ID INT)
AS
(
  SELECT * FROM DEPOSIT WHERE ACCOUNTNO=4;
  SELECT * FROM DEPOSIT WHERE ACCOUNTNO=:ID;
  EXECUTE DEPOSITID(:ID);
);

EXECUTE DEPOSITID(2);
Copy
+--------------+--------------+
| ACCOUNTNO    | ACCOUNTNAME  |
|--------------+--------------|
| 4            | Account 4    |
+--------------+--------------+

Copy
+--------------+--------------+
| ACCOUNTNO    | ACCOUNTNAME  |
|--------------+--------------|
| 2            | Account 2    |
+--------------+--------------+

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE DEPOSITID (ID FLOAT)
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "09/09/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    return_arr ARRAY := array_construct();
    tbl_nm VARCHAR;
  BEGIN
    tbl_nm := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
    CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_nm) AS
      SELECT
        * FROM
        DEPOSIT
      WHERE ACCOUNTNO=4;
    return_arr := array_append(return_arr, :tbl_nm);
    tbl_nm := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
    CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_nm) AS
      SELECT
        * FROM
        DEPOSIT
      WHERE ACCOUNTNO=:ID;
    return_arr := array_append(return_arr, :tbl_nm);
    CALL DEPOSITID(:ID);
    tbl_nm := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
    CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_nm) AS
      SELECT
        *
      FROM
        TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    return_arr := array_append(return_arr, :tbl_nm);
    --** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
    RETURN return_arr;
  END;
$$;

CALL DEPOSITID(2);
Copy
+-----------------------------------------------------+
| DEPOSIDID                                           |
|-----------------------------------------------------|
| [                                                   |
|  "RESULTSET_93D50CBB_F22C_418A_A88C_4E1DE101B500",  |
|  "RESULTSET_6BDE39D7_0554_406E_B52F_D9E863A3F15C"   |
| ]                                                   |
+-----------------------------------------------------+

Copy
Visualize Result Rets

Executing the above procedure on Snowflake, an array with temporary table names in it will be returned:

[ “RESULTSET_93D50CBB_F22C_418A_A88C_4E1DE101B500”, “RESULTSET_6BDE39D7_0554_406E_B52F_D9E863A3F15C”]

It is necessary to execute the following queries to display the result sets just like in Teradata.

 SELECT * FROM table('RESULTSET_93D50CBB_F22C_418A_A88C_4E1DE101B500');
SELECT * FROM table('RESULTSET_6BDE39D7_0554_406E_B52F_D9E863A3F15C');
Copy
+--------------+--------------+
| ACCOUNTNO    | ACCOUNTNAME  |
|--------------+--------------|
| 4            | Account 4    |
+--------------+--------------+

Copy
+--------------+--------------+
| ACCOUNTNO    | ACCOUNTNAME  |
|--------------+--------------|
| 2            | Account 2    |
+--------------+--------------+

Copy

Known Issues

No issues were found.

Related EWIs

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

CREATE PROCEDURE

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}
Copy

Sample Source Patterns

Setup data

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

 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);
Copy
 CREATE OR REPLACE TABLE inventory (
    product_name VARCHAR(50),
    price INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

INSERT INTO inventory
VALUES ('Bread', 50);

INSERT INTO inventory
VALUES ('Tuna', 150);

INSERT INTO inventory
VALUES ('Gum', 20);

INSERT INTO inventory
VALUES ('Milk', 80);
Copy

Basic Procedure

Teradata
 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';
Copy
+--------------+--------------+
| product_name |    price     |
|--------------+--------------|
| Salt         | 50           |
+--------------+--------------+

Copy
Snowflake Scripting
 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/23/2024" }}'
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 product_name = 'Salt';
Copy
+--------------+--------------+
| product_name |    price     |
|--------------+--------------|
| Salt         | 50           |
+--------------+--------------+

Copy

Single out parameter

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

CALL procedureLabelSingle(?);
Copy
Message                 |
------------------------+
Assignment value. Thanks|

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE procedureLabelSingle (
--                                                  OUT
                                                      MESSAGE VARCHAR(100))
RETURNS VARIANT
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';
        RETURN Message;
    END;
$$;

CALL procedureLabelSingle(?);
Copy
+───────────────────────────────+
| PROCEDURELABELSINGLE          |
+───────────────────────────────+
| ""Assignment value. Thanks""  |
+───────────────────────────────+

Copy

Multiple out parameter

Teradata
 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(?, ?);
Copy
1                       |2                        |
------------------------+-------------------------+
Assignment value. Thanks|Assignment value2. Thanks|

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE procedureLabelMultiple (
--                                                    OUT
                                                        MESSAGE VARCHAR(100),
--                                                                              OUT
                                                                                  MESSAGE2 VARCHAR(100))
RETURNS VARIANT
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';
        Message2 := 'Assignment value2. Thanks';
        RETURN OBJECT_CONSTRUCT('Message', :Message, 'Message2', :Message2);
    END;
$$;

CALL procedureLabelSingle(?, ?);
Copy
+─────────────────────────+────────────────────────────────+
| PROCEDURELABELMULTIPLE  |                                |
+─────────────────────────+────────────────────────────────+
| "{                      |                                |
| ""Message""             | ""Assignment value. Thanks"",  |
| ""Message2""            | ""Assignment value2. Thanks""  |
| }"                      |                                |
+─────────────────────────+────────────────────────────────+


Copy

Note

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

Snowflake Scripting
 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 VARCHAR(50), out !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'OUTPUT PARAMETERS ALONG WITH DYNAMIC RESULT SET' NODE ***/!!! PRICE integer)
RETURNS VARIANT
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
$$
	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": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/23/2024" }}'
EXECUTE AS CALLER
AS
$$
	DECLARE
				price INTEGER;
				productName varchar(10);
				call_results VARIANT;
	BEGIN
				 
				 
				call_results := (
			CALL Procedure1(:productName, :price)
				);
				productName := :call_results:product_name;
				price := :call_results:price;
				INSERT INTO inventory
				VALUES (:productName, :price);
	END;
$$;

CALL Procedure2();
Copy

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.

CURSOR

Description

A cursor is a data structure that is used by stored procedures at runtime to point to a resultset returned by an SQL query. For more information check here.

 DECLARE cursor_name [ SCROLL | NO SCROLL ] CURSOR
     [ 
          WITHOUT RETURN
          |
          WITH RETURN [ ONLY ] [ TO [ CALLER | CLIENT ] ]
     ]
     FOR
     cursor_specification [ FOR [ READ ONLY | UPDATE ] ]
     |
     statement_name
;
Copy
 FETCH [ [ NEXT | FIRST ] FROM ] cursor_name INTO
    [ variable_name | parameter_name ] [ ,...n ]
;
Copy
 OPEN cursor_name
    [ USING [ SQL_identifier | SQL_paramenter ] [ ,...n ] ]
;
Copy
 CLOSE cursor_name ;
Copy

Sample Source Patterns

Setup Data

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

 CREATE TABLE vEmployee(
    PersonID INT,
    LastName VARCHAR(255),
    FirstName VARCHAR(255)
);

CREATE TABLE ResTable(
    Column1 VARCHAR(255)
);

INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (1, 'Smith', 'Christian');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (2, 'Johnson', 'Jhon');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (3, 'Brown', 'William');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (4, 'Williams', 'Gracey');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (5, 'Garcia', 'Julia');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (6, 'Miller', 'Peter');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (7, 'Davis', 'Jannys');

CREATE TABLE TEST_TABLE (
    ColumnA NUMBER, 
    ColumnB VARCHAR(8),
    ColumnC VARCHAR(8));


SELECT * FROM TEST_TABLE;
INSERT INTO TEST_TABLE VALUES (1, '1', '1');
INSERT INTO TEST_TABLE VALUES (2, '2', '2');
Copy
 CREATE OR REPLACE TABLE vEmployee (
    PersonID INT,
    LastName VARCHAR(255),
    FirstName VARCHAR(255)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

CREATE OR REPLACE TABLE ResTable (
    Column1 VARCHAR(255)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES (1, 'Smith', 'Christian');

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES (2, 'Johnson', 'Jhon');

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES (3, 'Brown', 'William');

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES (4, 'Williams', 'Gracey');

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES (5, 'Garcia', 'Julia');

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES (6, 'Miller', 'Peter');

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES (7, 'Davis', 'Jannys');

CREATE OR REPLACE TABLE TEST_TABLE (
    ColumnA NUMBER(38, 18),
    ColumnB VARCHAR(8),
    ColumnC VARCHAR(8))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

SELECT
    * FROM
    TEST_TABLE;

    INSERT INTO TEST_TABLE
    VALUES (1, '1', '1');

    INSERT INTO TEST_TABLE
    VALUES (2, '2', '2');
Copy

Basic Cursor

Teradata
 REPLACE PROCEDURE CursorsTest()
BEGIN
    DECLARE val1 VARCHAR(255);
    DECLARE empcursor CURSOR FOR
        SELECT LastName
        FROM vEmployee
        ORDER BY PersonID;
    
    OPEN empcursor;
    FETCH NEXT FROM empcursor INTO val1;
    FETCH NEXT FROM empcursor INTO val1;
    INSERT INTO ResTable(Column1) VALUES (val1);
    CLOSE empcursor;
END;

CALL CursorsTest();
SELECT * FROM ResTable;
Copy
Johnson

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE CursorsTest ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "06/18/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        val1 VARCHAR(255);
    BEGIN
         
        LET empcursor CURSOR
        FOR
            SELECT
                LastName
                   FROM
                vEmployee
                   ORDER BY PersonID;
        OPEN empcursor;
        FETCH NEXT FROM empcursor INTO val1;
            FETCH NEXT FROM empcursor INTO val1;
        INSERT INTO ResTable (Column1)
        VALUES (:val1);
            CLOSE empcursor;
    END;
$$;

CALL CursorsTest();

SELECT
    * FROM
    ResTable;
Copy
Johnson

Copy

Single Returnable Cursor

The following procedure is intended to return one result set since it has the DYNAMIC RESULT SETS 1 property in the header, the cursor has the WITH RETURN property and is being opened in the body.

Teradata
 REPLACE PROCEDURE spSimple ()
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE result_set CURSOR WITH RETURN ONLY FOR
    SELECT *
    FROM vEmployee;
        
    OPEN result_set;
END;

CALL spSimple();
Copy
PersonID|LastName|FirstName|
--------+--------+---------+
       7|Davis   |Jannys   |
       5|Garcia  |Julia    |
       3|Brown   |William  |
       1|Smith   |Christian|
       6|Miller  |Peter    |
       4|Williams|Gracey   |
       2|Johnson |Jhon     |

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE spSimple ()
RETURNS TABLE (
)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET result_set CURSOR FOR
            SELECT * FROM vEmployee;
        OPEN result_set;
        RETURN TABLE(resultset_from_cursor(result_set));
    END;
$$;

CALL spSimple();
Copy
PERSONID|LASTNAME|FIRSTNAME|
--------+--------+---------+
       1|Smith   |Christian|
       2|Johnson |Jhon     |
       3|Brown   |William  |
       4|Williams|Gracey   |
       5|Garcia  |Julia    |
       6|Miller  |Peter    |
       7|Davis   |Jannys   |

Copy

Multiple Returnable Cursors

The following procedure is intended to return multiple results when DYNAMIC RESULT SETS property in the header is greater than 1, the procedure has multiple cursors with the WITH RETURN property and these same cursors are being opened in the body.

Teradata
 REPLACE PROCEDURE spTwoOrMore()
DYNAMIC RESULT SETS 2
BEGIN
    DECLARE result_set CURSOR WITH RETURN ONLY FOR
        SELECT * FROM SampleTable2;

    DECLARE result_set2 CURSOR WITH RETURN ONLY FOR
	SELECT Column11 FROM SampleTable1;
    OPEN result_set2;
    OPEN result_set;
END;

CALL spTwoOrMore();
Copy
ColumnA|ColumnB|ColumnC|
-------+-------+-------+
      2|2      |2      |
      1|1      |1      |

PersonID|LastName|FirstName|
--------+--------+---------+
       7|Davis   |Jannys   |
       5|Garcia  |Julia    |
       3|Brown   |William  |
       1|Smith   |Christian|
       6|Miller  |Peter    |
       4|Williams|Gracey   |
       2|Johnson |Jhon     |

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE spTwoOrMore ()
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
				SampleTable2;
		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
				Column11 FROM
				SampleTable1;
		LET result_set2 CURSOR
		FOR
			SELECT
				*
			FROM
				IDENTIFIER(?);
		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);
	END;
$$;

CALL spTwoOrMore();
Copy
[
  "RESULTSET_B5B0005D_1602_48B7_9EE4_62E1A28B000C",
  "RESULTSET_1371794D_7B77_4DA9_B42E_7981F35CEA9C"
]

ColumnA|ColumnB|ColumnC|
-------+-------+-------+
      2|2      |2      |
      1|1      |1      |

PersonID|LastName|FirstName|
--------+--------+---------+
       7|Davis   |Jannys   |
       5|Garcia  |Julia    |
       3|Brown   |William  |
       1|Smith   |Christian|
       6|Miller  |Peter    |
       4|Williams|Gracey   |
       2|Johnson |Jhon     |


Copy

Cursors With Binding Variables

The following cursor uses binding variables as the were condition to perform the query.

Teradata
 REPLACE PROCEDURE TestProcedure (IN param1 NUMBER, param2 VARCHAR(8), param3 VARCHAR(8))
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE cursorExample CURSOR WITH RETURN ONLY FOR
        SELECT * FROM  TEST_TABLE
   	WHERE ColumnA = param1 AND ColumnB LIKE param2 and ColumnC LIKE param3;
    
    OPEN cursorExample;	  
END;

CALL TestProcedure(2, '2', '2');
Copy
|ColumnA|ColumnB|ColumnC|
+-------+-------+-------+
|      2|2      |2      |

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE TestProcedure (PARAM1 NUMBER(38, 18), PARAM2 VARCHAR(8), PARAM3 VARCHAR(8))
RETURNS TABLE (
)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      LET cursorExample CURSOR
      FOR
         SELECT
            * FROM
            TEST_TABLE
           	WHERE ColumnA = param1 AND ColumnB LIKE param2 and ColumnC LIKE param3;
      OPEN cursorExample;
      RETURN TABLE(resultset_from_cursor(cursorExample));
   END;
$$;

CALL TestProcedure(2, '2', '2');
Copy
|ColumnA|ColumnB|ColumnC|
+-------+-------+-------+
|      2|2      |2      |

Copy

Cursor For Loop

It is a type of loop that uses a cursor to fetch rows from a SELECT statement and then performs some processing on each row.

Teradata
 REPLACE PROCEDURE TestProcedure ()
DYNAMIC RESULT SETS 1
BEGIN
    FOR fUsgClass AS cUsgClass CURSOR FOR
        SELECT columnA FROM  TEST_TABLE
    DO
        INSERT INTO ResTable(Column1) VALUES (fUsgClass.columnA);
    END FOR;
END;

CALL TestProcedure();
SELECT * FROM ResTable;
Copy
|Column1|
+-------+
|      1|
|      2|

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE TestProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET cUsgClass CURSOR FOR
        SELECT columnA FROM TEST_TABLE;
        --** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
        FOR fUsgClass IN cUsgClass DO
            INSERT INTO ResTable (Column1) VALUES (:temp_fUsgClass_columnA);
        END FOR;
    END;
$$;

CALL TestProcedure();

SELECT * FROM ResTable;
Copy
|Column1|
+-------+
|      1|
|      2|

Copy

Cursor Fetch inside a Loop

It allows one to retrieve rows from a result set one at a time and perform some processing on each row.

Teradata
 REPLACE PROCEDURE teradata_fetch_inside_loop() 
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE col_name VARCHAR(255);
    DECLARE col_int INTEGER DEFAULT 1;
    DECLARE cursor_var CURSOR FOR SELECT columnA FROM TEST_TABLE;
    WHILE (col_int <> 0) DO		
        FETCH cursor_var INTO col_name;
        INSERT INTO ResTable(Column1) VALUES (cursor_var.columnA);
        SET col_int = 0;
    END WHILE;
END;

CALL teradata_fetch_inside_loop();
SELECT * FROM ResTable;
Copy
|Column1|
+-------+
|      2|

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE teradata_fetch_inside_loop ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "06/18/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        col_name VARCHAR(255);
        col_int INTEGER DEFAULT 1;
    BEGIN
         
         
        LET cursor_var CURSOR
        FOR
            SELECT
                columnA FROM
                TEST_TABLE;
                WHILE (col_int <> 0) LOOP
            --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
                    FETCH cursor_var INTO col_name;
            INSERT INTO ResTable (Column1)
            VALUES (cursor_var.columnA);
            col_int := 0;
                END LOOP;
    END;
$$;

CALL teradata_fetch_inside_loop();

SELECT
    * FROM
    ResTable;
Copy
|Column1|
+-------+
|      2|

Copy

Known Issues

The following parameters are not applicable in Snowflake Scripting.

1. Declare

[ SCROLL/NO SCROLL ] Snowflake Scripting only supports FETCH NEXT.

[ READ-ONLY ] This is the default in Snowflake Scripting.

[ UPDATE ].

2. Fetch

[ NEXT ] This is the default behavior in Snowflake Scripting.

[ FIRST ].

Related EWIs

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

  2. SSC-PRF-0003: Fetch inside a loop is considered a complex pattern, this could degrade Snowflake performance.

  3. SSC-PRF-0004: This statement has usages of cursor for loop.

DECLARE CONTINUE HANDLER

Description

Handle completion conditions and exception conditions not severe enough to affect the flow of control.

For more information regarding the Teradata DECLARE CONTINUE handler, check here.

 DECLARE CONTINUE HANDLER FOR
  { 
    { sqlstate_state_spec | condition_name } [,...] |

    { SQLEXCEPTION | SQLWARNING | NOT FOUND } [,...]

  } handler_action_statement ;
Copy

Sample Source Patterns

DECLARE CONTINUE HANDLER

Teradata
 REPLACE PROCEDURE PURGING_ADD_TABLE
( 
 IN inDatabaseName     	VARCHAR(30), 
 IN inTableName    		VARCHAR(30)
)
BEGIN
 DECLARE vCHAR_SQLSTATE CHAR(5);
 DECLARE vSUCCESS       CHAR(5);

  DECLARE CONTINUE HANDLER FOR SQLSTATE 'T5628'
  BEGIN
     SET vCHAR_SQLSTATE = SQLCODE;
     SET vSUCCESS    = SQLCODE;
  END;

  SELECT 1;
 
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE PURGING_ADD_TABLE
(INDATABASENAME VARCHAR(30), INTABLENAME VARCHAR(30)
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "06/18/2024" }}'
EXECUTE AS CALLER
AS
$$
 DECLARE
  vCHAR_SQLSTATE CHAR(5);
  vSUCCESS       CHAR(5);
 BEGIN
   
   
  BEGIN
   SELECT
    1;
  EXCEPTION
   WHEN statement_error THEN
    LET errcode := :sqlcode
    LET sqlerrmsg := :sqlerrm
    IF (errcode = '904'
    AND contains(sqlerrmsg, 'invalid value')) THEN
     BEGIN
      vCHAR_SQLSTATE := SQLCODE;
      vSUCCESS := SQLCODE;
     END;
    ELSE
     RAISE
    END IF
  END
 END;
$$;
Copy

Known Issues

DECLARE CONTINUE HANDLER FOR SQLSTATE

The support of declaring continue handlers for some SQLSTATE values is not currently supported by Snowflake Scripting.

Teradata
 CREATE PROCEDURE declareConditionExample2 ( )
BEGIN
   DECLARE CONTINUE HANDLER FOR SQLSTATE 'UNSUPPORTED'
     BEGIN
       SET vCHAR_SQLSTATE = SQLCODE;
       SET vSUCCESS    = SQLCODE;
    END;
END;
Copy
Snowflake Scripting
CREATE OR REPLACE PROCEDURE declareConditionExample2 ( )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      !!!RESOLVE EWI!!! /*** SSC-EWI-TD0004 - NOT SUPPORTED SQL EXCEPTION ON CONTINUE HANDLER ***/!!!
      DECLARE CONTINUE HANDLER FOR SQLSTATE 'UNSUPPORTED'
      BEGIN
         vCHAR_SQLSTATE := SQLCODE;
         vSUCCESS := SQLCODE;
      END;
   END;
$$;
Copy

Related EWIS

  1. SSC-EWI-TD0004: Not supported SQL Exception on continue handler.

DECLARE CONDITION HANDLER

Description

Assign a name to an SQLSTATE code, or declare a user-defined condition.

For more information regarding the Teradata DECLARE CONDITION handler, check here.

 DECLARE condition_name CONDITION
    [ FOR SQLSTATE [ VALUE ] sqlstate_code ] ;
Copy

Sample Source Patterns

DECLARE CONDITION

Teradata
 CREATE PROCEDURE declareConditionExample ( )
BEGIN
    DECLARE DB_ERROR CONDITION;
    ...
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE declareConditionExample ( )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        DB_ERROR EXCEPTION;
    BEGIN
    END;
$$;
Copy

Known Issues

DECLARE CONDITION FOR SQLSTATE

The support of declaring conditions for SQLSTATE values is not currently supported by Snowflake Scripting.

Teradata
 CREATE PROCEDURE declareConditionExample2 ( )
BEGIN
    DECLARE ERROR_EXISTS CONDITION FOR SQLSTATE VALUE '42000';
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE declareConditionExample2 ( )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        ERROR_EXISTS EXCEPTION;
    BEGIN
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'SET EXCEPTION DETAILS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
-- ERROR_EXISTS CONDITION FOR SQLSTATE VALUE '42000';
    END;
$$;
Copy

Related EWIS

  1. SSC-EWI-0058: Functionality is not currently supported by Snowflake Scripting.

DECLARE

Description

Declares one or more local variables.

For more information regarding Teradata DECLARE, check here.

 DECLARE variable_name [, variable_name ]... DATA_TYPE [ DEFAULT default_value]
Copy

Sample Source Patterns

Teradata

 CREATE PROCEDURE declareExample ( )
BEGIN
    DECLARE COL_NAME, COL_TYPE VARCHAR(200) DEFAULT '' ;
    DECLARE COL_COUNT, COL_LEN INTEGER;
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE declareExample ( )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "06/18/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        COL_NAME VARCHAR(200) DEFAULT '';
        COL_TYPE VARCHAR(200) DEFAULT '';
        COL_COUNT INTEGER;
        COL_LEN INTEGER;
    BEGIN
         
         
        RETURN 1;
    END;
$$;
Copy

Known Issues

No issues were found.

Related EWIs

No related EWIs.

DML and DDL Objects

Description

DML and DDL objects are translated in the same way regardless of whether they are inside stored procedures or not. For further information check the following links.

Translation References

  • data-types.md: Compare Teradata data types and their equivalents in Snowflake.

  • ddl: Explore the translation of the Data Definition Language.

  • dml: Explore the translation of the Data Manipulation Language.

  • built-in-functions: Compare functions included in the runtime of both languages.

EXCEPTION HANDLERS

Note

Some parts in the output code are omitted for clarity reasons.

Description

Teradata’s single and multiple Exception Handlers are replaced by its equivalent handlers in Snowflake Scripting.

For more information regarding Teradata EXCEPTION HANDLERS, check here.

 DECLARE < handler_type > HANDLER
  FOR  < condition_value_list > < handler_action > ;
Copy

Sample Source Patterns

SQLEXCEPTION HANDLER

Teradata
 CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        INSERT INTO Proc_Error_Table ('procSample', 'Failed SqlException');
    SELECT * FROM Proc_Error_Table;
END;
Copy
 CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE ConditionByUser1 CONDITION;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        INSERT INTO Proc_Error_Table ('procSample', 'Failed SqlException');
    DECLARE EXIT HANDLER FOR ConditionByUser1
        INSERT INTO Proc_Error_Table ('procSample', 'Failed ConditionByUser1');
    SELECT * FROM Proc_Error_Table;
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE handlerSample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
         
        SELECT
            * FROM
            Proc_Error_Table;
    EXCEPTION
            WHEN other THEN
            INSERT INTO Proc_Error_Table
            VALUES ('procSample', 'Failed SqlException');
    END;
$$;
Copy
 CREATE OR REPLACE PROCEDURE handlerSample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        ConditionByUser1 EXCEPTION;
    BEGIN
         
         
        SELECT
            * FROM
            Proc_Error_Table;
    EXCEPTION
            WHEN ConditionByUser1 THEN
            INSERT INTO Proc_Error_Table
            VALUES ('procSample', 'Failed ConditionByUser1');
            WHEN other THEN
            INSERT INTO Proc_Error_Table
            VALUES ('procSample', 'Failed SqlException');
    END;
$$;
Copy

User-Defined Handlers

Teradata
 CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE EXIT HANDLER FOR Custom1, Custom2, Custom3
      BEGIN
        SET Message1 = 'custom1 and custom2 and custom3';
      END;
    SELECT * FROM Proc_Error_Table;
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE handlerSample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
         
        SELECT
            * FROM
            Proc_Error_Table;
    EXCEPTION
            WHEN Custom1 OR Custom2 OR Custom3 THEN
            BEGIN
                    Message1 := 'custom1 and custom2 and custom3';
            END;
    END;
$$;
Copy

Known Issues

CONTINUE Handler

A ‘CONTINUE’ handler in Teradata allows the execution to be resumed after executing a statement with errors. This is not supported by the exception blocks in Snowflake Scripting. Condition Handler Teradata reference documentation.

Teradata
 CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        INSERT INTO Proc_Error_Table ('spSample4', 'Failed SqlException');
    SELECT * FROM Proc_Error_Table;
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE handlerSample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-TD0004 - NOT SUPPORTED SQL EXCEPTION ON CONTINUE HANDLER ***/!!!
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        INSERT INTO Proc_Error_Table
        VALUES ('spSample4', 'Failed SqlException');
        SELECT
            * FROM
            Proc_Error_Table;
    END;
$$;
Copy

Other not supported handlers

Handlers for SQLSTATE, SQLWARNING, and NOT FOUND are not supported

Teradata
 CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '42002', SQLWARNING, NOT FOUND
        INSERT INTO Proc_Error_Table ('procSample', 'Failed SqlState or SqlWarning or Not Found');
    SELECT * FROM Proc_Error_Table;
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE handlerSample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/04/2024" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'SQLSTATE, SQLWARNING, NOT-FOUND TYPES HANDLER' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--        DECLARE EXIT HANDLER FOR SQLSTATE '42002', SQLWARNING, NOT FOUND
--            INSERT INTO Proc_Error_Table ('procSample', 'Failed SqlState or SqlWarning or Not Found');
        SELECT
            * FROM
            Proc_Error_Table;
    END;
$$;
Copy

Related EWIS

  1. SSC-EWI-0058: Functionality is not currently supported by Snowflake Scripting.

  2. SSC-EWI-TD0004: Not supported SQL Exception on continue handler.

EXECUTE/EXEC

Note

Some parts in the output code are omitted for clarity reasons.

Description

The Teradata EXECUTEstatement allows the execution prepared dynamic SQL or macros, on the other hand exec only allows macros.

For more information regarding Teradata EXECUTE/EXEC, check Macro Form and Dynamic SQL Form

 -- EXECUTE macro syntax
{EXECUTE | EXEC } macro_identifier [ (<parameter_definition>[, ...n] ) ] [;]  

<parameter_definition>:= {parameter_name = constant_expression | constant_expresion}


-- EXECUTE prepared dynamic SQL syntax
EXECUTE prepare_indentifier [<using>|<usingDescriptor>]

<using>:= USING < host_variable >[, ...n]
<host_variable>:= [:] host_variable_name [[INDICATOR] :host_indicator_name]
<usingDescriptor>:= USING DESCRIPTOR [:] descript_area
Copy

Sample Source Patterns

Setup data

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

 -- Additional Params: -t JavaScript
CREATE TABLE inventory (
    product_name VARCHAR(50),
    price INTEGER
);

CREATE MACRO dummyMacro AS(
  SELECT * FROM INVENTORY;
);
Copy
 CREATE OR REPLACE TABLE inventory (
  product_name VARCHAR(50),
  price INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

CREATE OR REPLACE PROCEDURE dummyMacro ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
  // SnowConvert Helpers Code section is omitted.
  
  INSERT_TEMP(`SELECT
   *
  FROM
   INVENTORY`,[]);
  return tablelist;
$$;
Copy

Execute prepared statement

Teradata
 CREATE PROCEDURE InsertProductInInventory(IN productName VARCHAR(50), IN price INTEGER)
BEGIN
    DECLARE dynamicSql CHAR(200);
    SET dynamicSql = 'INSERT INTO INVENTORY VALUES( ?, ?)';
    PREPARE preparedSql FROM dynamicSql;
    EXECUTE preparedSql USING productName, price;
    
END;

CALL InsertProductInInventory('''Chocolate''', 75);
CALL InsertProductInInventory('''Sugar''', 65);
CALL InsertProductInInventory('''Rice''', 100);
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE InsertProductInInventory (PRODUCTNAME VARCHAR(50), PRICE INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        dynamicSql CHAR(200);
    BEGIN
         
        dynamicSql := 'INSERT INTO INVENTORY
VALUES (?, ?)';
        !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PREPARE STATEMENT' NODE ***/!!!
            PREPARE preparedSql FROM dynamicSql;
        !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
        EXECUTE IMMEDIATE dynamicSql;
    END;
$$;

CALL InsertProductInInventory('''Chocolate''', 75);

CALL InsertProductInInventory('''Sugar''', 65);

CALL InsertProductInInventory('''Rice''', 100);
Copy

Execute macro statement

Teradata
 EXECUTE dummyMacro;
Copy
+---------------+-------+
| product_name  | price |
+---------------+-------+
| 'Chocolate'   | 75    |
+---------------+-------+
| 'Sugar'       | 65    |
+---------------+-------+
| 'Rice'        | 100   |
+---------------+-------+

Copy
Snowflake Scripting
 !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE dummyMacro;
Copy

Related EWIs

  1. SSC-EWI-0030: The statement below has usages of dynamic SQL.

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

EXECUTE IMMEDIATE

Note

Some parts in the output code are omitted for clarity reasons.

Description

The Teradata EXECUTE IMMEDIATE statement allows the execution of dynamic SQL contained on variables or string literals.

For more information about EXECUTE IMMEDIATE click here.

 -- EXECUTE IMMEDIATE syntax
EXECUTE IMMEDIATE <dynamic_statement>

<dynamic_statement> := {string_literal | string_variable}
Copy

Sample Source Patterns

Setup data

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

 CREATE TABLE inventory (
    product_name VARCHAR(50),
    price INTEGER
);
Copy
 CREATE OR REPLACE TABLE inventory (
    product_name VARCHAR(50),
    price INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;
Copy

Execute Example

Teradata
 REPLACE PROCEDURE InsertProductInInventory(IN productName VARCHAR(50), IN price INTEGER)
BEGIN
	DECLARE insertStatement VARCHAR(100);
	SET insertStatement = 'INSERT INTO INVENTORY VALUES(' || productName || ', ' || price || ')';
    EXECUTE IMMEDIATE insertStatement;
END;

CALL InsertProductInInventory('''Chocolate''', 75);
CALL InsertProductInInventory('''Sugar''', 65);
CALL InsertProductInInventory('''Rice''', 100);

SELECT product_name, price FROM inventory;
Copy
+--------------+-------+
| product_name | price |
+--------------+-------+
| Chocolate    | 75    |
+--------------+-------+
| Sugar        | 65    |
+--------------+-------+
| Rice         | 100   |
+--------------+-------+

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE InsertProductInInventory (PRODUCTNAME VARCHAR(50), PRICE INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		insertStatement VARCHAR(100);
	BEGIN
		 
		insertStatement := 'INSERT INTO INVENTORY
VALUES (' || productName || ', ' || price || ')';
		!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
		EXECUTE IMMEDIATE insertStatement;
	END;
$$;

CALL InsertProductInInventory('''Chocolate''', 75);

CALL InsertProductInInventory('''Sugar''', 65);

CALL InsertProductInInventory('''Rice''', 100);

SELECT
	product_name,
	price FROM
	inventory;
Copy
+--------------+-------+
| PRODUCT_NAME | PRICE |
+--------------+-------+
| Chocolate    | 75    |
+--------------+-------+
| Sugar        | 65    |
+--------------+-------+
| Rice         | 100   |
+--------------+-------+

Copy
column1|column2                  |column3|
-------+-------------------------+-------+
      3|Mundo3                   |    3.3|

Copy

Related EWIS

  1. SSC-EWI-0030: The statement below has usages of dynamic SQL.

FUNCTION OPTIONS OR DATA ACCESS

Note

Some parts in the output code are omitted for clarity reasons.

Note

Non-relevant statement.

Warning

Notice that this statement isremoved from the migrationbecause it is a non-relevant syntax. It means that it is not required in Snowflake.

Description

Functions options or data access options are statements used in functions on the declaration part to specify certain characteristics. These can be:

  • CONTAINS SQL

  • SQL SECURITY DEFINER

  • COLLATION INVOKER

  • SPECIFIC FUNCTION_NAME

Sample Source Patterns

Function Options

Notice that in this example the function options have been removed because they are not required in Snowflake.

 CREATE FUNCTION sumValues(A INTEGER, B INTEGER)
   RETURNS INTEGER
   LANGUAGE SQL
   CONTAINS SQL
   SQL SECURITY DEFINER
   SPECIFIC sumTwoValues
   COLLATION INVOKER
   INLINE TYPE 1
   RETURN A + B;
Copy
 CREATE OR REPLACE FUNCTION sumValues (A INTEGER, B INTEGER)
   RETURNS INTEGER
   LANGUAGE SQL
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
   AS
   $$
      A + B
   $$;
Copy

Known Issues

No issues were found.

Related EWIs

No related EWIs.

GET DIAGNOSTICS EXCEPTION

Note

Some parts in the output code are omitted for clarity reasons.

Description

GET DIAGNOSTICS retrieves information about successful, exception, or completion conditions from the Diagnostics Area.

For more information regarding Teradata GET DIAGNOSTICS, check here.

 GET DIAGNOSTICS
{
  [ EXCEPTION < condition_number >
    [ < parameter_name | variable_name > = < information_item > ]...
  ] 
  |
  [ < parameter_name | variable_name > = < information_item > ]...
}
Copy

Sample Source Patterns

Teradata

 CREATE PROCEDURE getDiagnosticsSample ()
BEGIN
    DECLARE V_MESSAGE, V_CODE VARCHAR(200);
    DECLARE V_Result INTEGER;
    
    SELECT c1 INTO V_Result FROM tab1;
    GET DIAGNOSTICS EXCEPTION 1
        V_MESSAGE = Message_Text,
        V_CODE = RETURNED_SQLSTATE;
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE getDiagnosticsSample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "06/18/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        V_MESSAGE VARCHAR(200);
        V_CODE VARCHAR(200);
        V_Result INTEGER;
    BEGIN
         
         
        SELECT
            c1 INTO
            :V_Result
        FROM
            tab1;
            V_MESSAGE := SQLERRM;
            V_CODE := SQLSTATE;
    END;
$$;
Copy

Known Issues

CLASS_ORIGIN, CONDITION_NUMBER

The use of GET DIAGNOSTICS for CLASS_ORIGIN, CONDITION_NUMBER is not supported

Teradata
 CREATE PROCEDURE getDiagnosticsSample ()
BEGIN
    DECLARE V_MESSAGE, V_CODE VARCHAR(200);
    DECLARE V_Result INTEGER;
    
    SELECT c1 INTO V_Result FROM tab1;
    GET DIAGNOSTICS EXCEPTION 5
        V_CLASS = CLASS_ORIGIN,
        V_COND = CONDITION_NUMBER;
END;
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE getDiagnosticsSample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "06/18/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        V_MESSAGE VARCHAR(200);
        V_CODE VARCHAR(200);
        V_Result INTEGER;
    BEGIN
         
         
        SELECT
            c1 INTO
            :V_Result
        FROM
            tab1;
--            V_CLASS = CLASS_ORIGIN
                                  !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'GET DIAGNOSTICS DETAIL FOR CLASS_ORIGIN' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
             
--            V_COND = CONDITION_NUMBER
                                     !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'GET DIAGNOSTICS DETAIL FOR CONDITION_NUMBER' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
             
    END;
$$;
Copy

Related EWIS

  1. SSC-EWI-0058: Functionality is not currently supported by Snowflake Scripting.

IF

Note

Some parts in the output code are omitted for clarity reasons.

Description

Provides conditional execution based on the truth value of a condition.

For more information regarding Teradata IF, check here.

 IF conditional_expression THEN
     statement
     [ statement ]... 
[ ELSEIF conditional_expression THEN
     statement
     [ statement ]... ]...
[ ELSE   
     statement
     [ statement ]... ]
END IF;
Copy

Sample Source Patterns

Sample auxiliar table

 CREATE TABLE if_table(col1 varchar(30));
Copy
 CREATE OR REPLACE TABLE if_table (
col1 varchar(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;
Copy

Possible IF variations

Teradata
 CREATE PROCEDURE ifExample1 ( flag NUMBER )
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   END IF;
END;

CALL ifExample1(1);
SELECT * FROM if_table;
Copy
 CREATE PROCEDURE ifExample2 ( flag NUMBER )
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   ELSE
      INSERT INTO if_table(col1) VALUES ('Unexpected input.');
   END IF;
END;

CALL ifExample2(2);
SELECT * FROM if_table;
Copy
 CREATE PROCEDURE ifExample3 ( flag NUMBER )
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   ELSEIF flag = 2 THEN
      INSERT INTO if_table(col1) VALUES ('two');
   ELSEIF flag = 3 THEN
      INSERT INTO if_table(col1) VALUES ('three');
   END IF;
END;

CALL ifExample3(3);
SELECT * FROM if_table;
Copy
 CREATE PROCEDURE ifExample4 ( flag NUMBER )
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   ELSEIF flag = 2 THEN
      INSERT INTO if_table(col1) VALUES ('two');
   ELSEIF flag = 3 THEN
      INSERT INTO if_table(col1) VALUES ('three');
   ELSE
      INSERT INTO if_table(col1) VALUES ('Unexpected input.');  
   END IF;
END;

CALL ifExample4(4);
SELECT * FROM if_table;
Copy
|COL1|
|----|
|one |


Copy
|COL1             |
|-----------------|
|Unexpected input.|


Copy
|COL1 |
|-----|
|three|


Copy
|COL1             |
|-----------------|
|Unexpected input.|


Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE ifExample1 (FLAG NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF (flag = 1) THEN
         INSERT INTO if_table (col1)
         VALUES ('one');
      END IF;
   END;
$$;

CALL ifExample1(1);

SELECT
   * FROM
   if_table;
Copy
 CREATE OR REPLACE PROCEDURE ifExample2 (FLAG NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF (flag = 1) THEN
         INSERT INTO if_table (col1)
         VALUES ('one');
      ELSE
         INSERT INTO if_table (col1)
         VALUES ('Unexpected input.');
      END IF;
   END;
$$;

CALL ifExample2(2);

SELECT
   * FROM
   if_table;
Copy
 CREATE OR REPLACE PROCEDURE ifExample3 (FLAG NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF (flag = 1) THEN
         INSERT INTO if_table (col1)
         VALUES ('one');
      ELSEIF (flag = 2) THEN
         INSERT INTO if_table (col1)
         VALUES ('two');
      ELSEIF (flag = 3) THEN
         INSERT INTO if_table (col1)
         VALUES ('three');
      END IF;
   END;
$$;

CALL ifExample3(3);

SELECT
   * FROM
   if_table;
Copy
 CREATE OR REPLACE PROCEDURE ifExample4 (FLAG NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF (flag = 1) THEN
         INSERT INTO if_table (col1)
         VALUES ('one');
      ELSEIF (flag = 2) THEN
         INSERT INTO if_table (col1)
         VALUES ('two');
      ELSEIF (flag = 3) THEN
         INSERT INTO if_table (col1)
         VALUES ('three');
      ELSE
         INSERT INTO if_table (col1)
         VALUES ('Unexpected input.');
      END IF;
   END;
$$;

CALL ifExample4(4);

SELECT
   * FROM
   if_table;
Copy
|COL1|
|----|
|one |


Copy
|COL1             |
|-----------------|
|Unexpected input.|


Copy
|COL1 |
|-----|
|three|


Copy
|COL1             |
|-----------------|
|Unexpected input.|


Copy

Known Issues

No issues were found.

Related EWIs

No related EWIs.

LOCKING FOR ACCESS

Note

Some parts in the output code are omitted for clarity reasons.

Note

Non-relevant statement.

Warning

Notice that this statement isremoved from the migrationbecause it is a non-relevant syntax. It means that it is not required in Snowflake.

Description

The functionality of locking a row in Teradata is related to the access and the privileges. Revire the following documentation to know more.

Sample Source Patterns

Locking row

Notice that in this example the LOCKING ROW FOR ACCESS has been deleted. This is because Snowflake handles accesses with roles and privileges. The statement is not required.

 REPLACE VIEW SCHEMA2.VIEW1
AS 
LOCKING ROW FOR ACCESS
SELECT * FROM SCHEMA1.TABLE1;
Copy
 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "SCHEMA1.TABLE1" **
CREATE OR REPLACE VIEW SCHEMA2.VIEW1
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
AS
--** SSC-FDM-0001 - VIEWS SELECTING ALL COLUMNS FROM A SINGLE TABLE ARE NOT REQUIRED IN SNOWFLAKE AND MAY IMPACT PERFORMANCE. **
SELECT
* FROM
SCHEMA1.TABLE1;
Copy

Known Issues

No issues were found.

Related EWIs

  1. SSC-FDM-0001: Views selecting all columns from a single table are not required in Snowflake.

  2. SSC-FDM-0007: Element with missing dependencies.

LOOP

Note

Some parts in the output code are omitted for clarity reasons.

Description

Teradata’s LOOP statement is translated to Snowflake Scripting LOOP syntax.

For more information on Teradata Loop, check here.

 [label_name:] LOOP
    { sql_statement }
END LOOP [label_name];
Copy

Sample Source Patterns

Teradata

 CREATE PROCEDURE loopProcedure(OUT resultCounter INTEGER)
BEGIN
    DECLARE counter INTEGER DEFAULT 0;
   
    customeLabel: LOOP 
    	SET counter = counter + 1;
	IF counter = 10 THEN
	    LEAVE customeLabel;
	END IF;
    END LOOP customeLabel;
   
    SET resultCounter = counter;
END;

CALL loopProcedure(:?);
Copy
 |resultCounter|
|-------------|
|10           |
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE loopProcedure (
--                                           OUT
                                               RESULTCOUNTER INTEGER)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		counter INTEGER DEFAULT 0;
	BEGIN
		 
		LOOP
			counter := counter + 1;
			IF (counter = 10) THEN
				BREAK CUSTOMELABEL;
			END IF;
		END LOOP CUSTOMELABEL;
		resultCounter := counter;
		RETURN resultCounter;
	END;
$$;

CALL loopProcedure(:?);
Copy
 |LOOPPROCEDURE|
|-------------|
|10           |
Copy

Known Issues

No issues were found.

Related EWIs

No related EWIs.

OUTPUT PARAMETERS

Note

Some parts in the output code are omitted for clarity reasons.

Description

An output parameter is a parameter whose value is passed out of the stored procedure, back to the calling statement. Since the output parameters are not supported by Snowflake Scripting, a solution has been implemented in order to emulate their functionality.

Sample Source Patterns

Sample auxiliary table

 CREATE TABLE table20 ( col1 NUMBER, col2 NUMBER );
Copy
 CREATE OR REPLACE TABLE table20 (
col1 NUMBER(38, 18),
col2 NUMBER(38, 18)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;
Copy

In the declaration, the OUT or IN OUT keywords are removed. The assignment is being emitted the same as the input, but to emulate the functionality of the output parameter, some statements are being added.

When a procedure with output parameters is called into another one, some statements are added to get and assign the value(s) to the respective argument(s).

Single out parameter

CREATE PROCEDURE demo.proc_with_single_output_parameters(OUT param1 NUMBER)
BEGIN
 SET param1 = 100;
END;

REPLACE PROCEDURE demo.proc_calling_proc_with_single_output_parameters ()
BEGIN
  DECLARE mytestvar NUMBER;
  CALL demo.proc_with_single_output_parameters(mytestvar);
  INSERT INTO demo.TABLE20 VALUES(mytestvar,432);
END;
Copy
 CREATE OR REPLACE PROCEDURE demo.proc_with_single_output_parameters (
--                                                                     OUT
                                                                         PARAM1 NUMBER(38, 18))
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
 BEGIN
  param1 := 100;
  RETURN null;
 END;
$$;

CREATE OR REPLACE PROCEDURE demo.proc_calling_proc_with_single_output_parameters ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
 DECLARE
  mytestvar NUMBER(38, 18);
 BEGIN
   
  CALL demo.proc_with_single_output_parameters(:mytestvar);
  INSERT INTO demo.TABLE20
  VALUES (:mytestvar,432);
 END;
$$;
Copy

Multiple out parameter

 CREATE PROCEDURE demo.proc_with_multiple_output_parameters(OUT param1 NUMBER, INOUT param2 NUMBER)
BEGIN
  SET param1 = param2;
  SET param2 = 32;
END;

CREATE PROCEDURE demo.proc_calling_proc_with_multiple_output_parameters ()
BEGIN
    DECLARE var1  NUMBER;
    DECLARE var2  NUMBER;
    SET var2 = 34;
    CALL demo.proc_with_multiple_output_parameters(var1, var2);
    INSERT INTO demo.TABLE20 VALUES(var1,var2);
END;
Copy
 CREATE OR REPLACE PROCEDURE demo.proc_with_multiple_output_parameters (
--                                                                       OUT
                                                                           PARAM1 NUMBER(38, 18),
--                                                                                                  INOUT
                                                                                                        PARAM2 NUMBER(38, 18))
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
  BEGIN
    param1 := param2;
    param2 := 32;
    RETURN null;
  END;
$$;

CREATE OR REPLACE PROCEDURE demo.proc_calling_proc_with_multiple_output_parameters ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    var1 NUMBER(38, 18);
    var2 NUMBER(38, 18);
  BEGIN
     
     
    var2 := 34;
    CALL demo.proc_with_multiple_output_parameters(:var1, :var2);
    INSERT INTO demo.TABLE20
    VALUES (:var1, :var2);
  END;
$$;
Copy

Customer data type OUT parameters

when the output parameter is a customer type, the process is similar to a regular data type.

 CREATE OR REPLACE PROCEDURE GetEmployeeInfo (
    OUT EmpInfo EmployeeType
)
BEGIN
    SET EmpInfo.EmployeeID = 1001;
    SET EmpInfo.FirstName = 'John';
END;
Copy
 CREATE OR REPLACE PROCEDURE GetEmployeeInfo (
--   OUT
       EMPINFO EmployeeType
)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        EmpInfo.EmployeeID := 1001;
        EmpInfo.FirstName := 'John';
        RETURN EmpInfo;
    END;
$$;
Copy

Known Issues

1. Some data types may not work properly

As seen in the transformation, when retrieving the value from the called procedures, an implicit cast is performed from VARIANT to the type specified by the variable. Since there are a lot of possible data types, some casts may fail or contain different data.

Related EWIs

No related EWIs.

PREPARE

Description

Prepares the dynamic DECLARE CURSOR statement to allow the creation of different result sets. Allows dynamic parameter markers.

For more information, please review the following documentation.

Tedarata syntax:

 PREPARE statement_name FROM { 'statement_string' | statement_string_variable } ;
Copy

Where:

  • statement_name is the same identifier as statement_name in a DECLARE CURSOR statement.

  • statement_string is the SQL text that is to be executed dynamically.

  • statement_string_variable is the name of an SQL local variable, or an SQL parameter or string variable, that contains the SQL text string to be executed dynamically.

Note

Important information

For this transformation, the cursors are renamed since they cannot be dynamically updated.

Sample Source Patterns

Data setting for examples

For this example, please use the following complementary queries in the case that you want to run each case.

 CREATE TABLE MyTemporaryTable(
    Col1  INTEGER
);

INSERT INTO MyTemporaryTable(col1) VALUES (1);
SELECT * FROM databaseTest.MyTemporaryTable;


CREATE TABLE MyStatusTable (
    Col1  VARCHAR(2)
);
SELECT * FROM MyStatusTable;
Copy
 CREATE TABLE MyTemporaryTable (
    Col1 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

INSERT INTO MyTemporaryTable (col1) VALUES (1);

SELECT * FROM MyTemporaryTable;

    CREATE TABLE MyStatusTable (
    Col1 VARCHAR(2)
   )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

SELECT * FROM MyStatusTable;
Copy

Simple scenario

This example reviews the functionality for the cases where a single cursor is being used one single time.

Teradata
 REPLACE PROCEDURE simple_scenario()
BEGIN
    --Variables for the example's procedure_results
    DECLARE SQL_string_sel VARCHAR(200) DEFAULT 'SELECT * FROM MyTemporaryTable';
    DECLARE procedure_result INTEGER DEFAULT 0;

    -- Actual Cursor usage
    DECLARE C1 CURSOR FOR S1;
    PREPARE S1 FROM SQL_string_sel;
    OPEN C1;
    FETCH C1 INTO procedure_result;
    INSERT INTO databaseTest.MyStatusTable(Col1) VALUES (procedure_result);
    CLOSE C1;
END;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;
Copy

Col1

1

Snowflake Scripting

Note

Usages for cursors must be renamed and declared again.

 CREATE OR REPLACE PROCEDURE simple_scenario ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ ""origin"": ""sf_sc"", ""name"": ""snowconvert"", ""version"": {  ""major"": 0,  ""minor"": 0,  ""patch"": ""0"" }, ""attributes"": {  ""component"": ""none"",  ""convertedOn"": ""01/01/0001"" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    --Variables for the example's procedure_results
    SQL_string_sel VARCHAR(200) DEFAULT 'SELECT
   * FROM
   MyTemporaryTable';
    procedure_result INTEGER DEFAULT 0;
    S1 RESULTSET;
    prepareQuery_aux_sql VARCHAR;
  BEGIN
     
    -- Actual Cursor usage
     
    prepareQuery_aux_sql := SQL_string_sel;
    S1 := (
      EXECUTE IMMEDIATE prepareQuery_aux_sql
    );
    LET CURSOR_S1_INSTANCE_V0 CURSOR
    FOR
      S1;
    OPEN CURSOR_S1_INSTANCE_V0;
    FETCH
      CURSOR_S1_INSTANCE_V0
    INTO
      procedure_result;
    INSERT INTO databaseTest.MyStatusTable (Col1)
    VALUES (procedure_result);
    CLOSE CURSOR_S1_INSTANCE_V0;
  END;
$$;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;
Copy

Col1

1

Simple scenario with RETURN ONLY

Teradata
 REPLACE PROCEDURE simple_scenario()
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE SQL_string_sel VARCHAR(200) DEFAULT 'SELECT * FROM MyTemporaryTable';
    DECLARE procedure_result VARCHAR(100);
    DECLARE C1 CURSOR WITH RETURN ONLY FOR S1;

    SET procedure_result = '';
    PREPARE S1 FROM SQL_string_sel;
    OPEN C1;
END;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;
Copy

Col1

1

Snowflake Scripting

Note

Usages for cursors must be renamed and declared again.

 CREATE OR REPLACE PROCEDURE simple_scenario ()
RETURNS TABLE (
)
LANGUAGE SQL
COMMENT = '{ ""origin"": ""sf_sc"", ""name"": ""snowconvert"", ""version"": {  ""major"": 0,  ""minor"": 0,  ""patch"": ""0"" }, ""attributes"": {  ""component"": ""none"",  ""convertedOn"": ""01/01/0001"" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    SQL_string_sel VARCHAR(200) DEFAULT 'SELECT
   * FROM
   MyTemporaryTable';
    procedure_result VARCHAR(100);
    S1 RESULTSET;
    prepareQuery_aux_sql VARCHAR;
  BEGIN
     
     
    procedure_result := '';
    prepareQuery_aux_sql := SQL_string_sel;
    S1 := (
      EXECUTE IMMEDIATE prepareQuery_aux_sql
    );
    LET CURSOR_S1_INSTANCE_V0 CURSOR
    FOR
      S1;
    OPEN CURSOR_S1_INSTANCE_V0;
    RETURN TABLE(resultset_from_cursor(CURSOR_S1_INSTANCE_V0));
  END;
$$;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;
Copy

Col1

1

Reused cursor case

Teradata
 CREATE PROCEDURE fetch_simple_reused_cursor(OUT procedure_result INTEGER)
BEGIN
    DECLARE SQL_string_sel VARCHAR(200) DEFAULT 'SELECT col1 FROM MyTemporaryTable WHERE col1 = 1';

    DECLARE C1 CURSOR FOR S1;
    PREPARE S1 FROM SQL_string_sel;
    OPEN C1;
    FETCH C1 INTO procedure_result;
    CLOSE C1;

    PREPARE S1 FROM SQL_string_sel;
    OPEN C1;
    FETCH C1 INTO procedure_result;
    CLOSE C1;
END;
Copy
No returning information.

Copy
Snowflake Scripting

Note

Usages for cursors must be renamed and declared again.

 CREATE OR REPLACE PROCEDURE fetch_simple_reused_cursor (
--                                                        OUT
                                                            PROCEDURE_RESULT INTEGER)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        SQL_string_sel VARCHAR(200) DEFAULT 'SELECT
   col1 FROM
   MyTemporaryTable
WHERE col1 = 1';
        S1 RESULTSET;
        prepareQuery_aux_sql VARCHAR;
    BEGIN
         
        prepareQuery_aux_sql := SQL_string_sel;
        S1 := (
            EXECUTE IMMEDIATE prepareQuery_aux_sql
        );
        LET CURSOR_S1_INSTANCE_V0 CURSOR
        FOR
            S1;
        OPEN CURSOR_S1_INSTANCE_V0;
            FETCH
            CURSOR_S1_INSTANCE_V0
        INTO procedure_result;
            CLOSE CURSOR_S1_INSTANCE_V0;
        prepareQuery_aux_sql := SQL_string_sel;
        S1 := (
            EXECUTE IMMEDIATE prepareQuery_aux_sql
        );
        LET CURSOR_S1_INSTANCE_V1 CURSOR
        FOR
            S1;
        OPEN CURSOR_S1_INSTANCE_V1;
            FETCH
            CURSOR_S1_INSTANCE_V1
        INTO procedure_result;
            CLOSE CURSOR_S1_INSTANCE_V1;
        RETURN procedure_result;
    END;
$$;
Copy
No returning information.

Copy

Modified query before usage

Teradata
 REPLACE PROCEDURE fetch_modified_query_cursor()
BEGIN
    --Variables for the example's procedure_results
    DECLARE SQL_string_sel VARCHAR(200) DEFAULT 'SELECT col1 FROM MyTemporaryTable WHERE col1 = 1';
    DECLARE procedure_result INTEGER DEFAULT 0;
    -- Actual Cursor usages
    DECLARE C1 CURSOR FOR S1;
    PREPARE S1 FROM SQL_string_sel;

    -- This modification does not take effect since S1 is already staged for the Cursor
    SET SQL_string_sel = 'SELECT col1 FROM MyTemporaryTable WHERE col1 = 0';
    OPEN C1;
    FETCH C1 INTO procedure_result;
    INSERT INTO databaseTest.MyStatusTable(Col1) VALUES (procedure_result);
    CLOSE C1;
END;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;
Copy

Col1

1

Snowflake Scripting

Note

Usages for cursors must be renamed and declared again.

 CREATE OR REPLACE PROCEDURE fetch_modified_query_cursor ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ ""origin"": ""sf_sc"", ""name"": ""snowconvert"", ""version"": {  ""major"": 0,  ""minor"": 0,  ""patch"": ""0"" }, ""attributes"": {  ""component"": ""none"",  ""convertedOn"": ""01/01/0001"" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    --Variables for the example's procedure_results
    SQL_string_sel VARCHAR(200) DEFAULT 'SELECT
   col1 FROM
   MyTemporaryTable
WHERE col1 = 1';
    procedure_result INTEGER DEFAULT 0;
    S1 RESULTSET;
    prepareQuery_aux_sql VARCHAR;
  BEGIN
     
    -- Actual Cursor usages
     
    prepareQuery_aux_sql := SQL_string_sel;
    S1 := (
      EXECUTE IMMEDIATE prepareQuery_aux_sql
    );
    LET CURSOR_S1_INSTANCE_V0 CURSOR
    FOR
      S1;
    -- This modification does not take effect since S1 is already staged for the Cursor
    SQL_string_sel := 'SELECT
   col1 FROM
   MyTemporaryTable
WHERE col1 = 0';
    OPEN CURSOR_S1_INSTANCE_V0;
    FETCH
      CURSOR_S1_INSTANCE_V0
    INTO
      procedure_result;
    INSERT INTO databaseTest.MyStatusTable (Col1)
    VALUES (procedure_result);
    CLOSE CURSOR_S1_INSTANCE_V0;
  END;
$$;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;
Copy

Col1

1

Simple cursor combined with no PREPARE pattern

Teradata
 REPLACE PROCEDURE fetch_cursor_ignored_query_cursor()
BEGIN
    --Variables for the example's procedure_results
    DECLARE SQL_string_sel VARCHAR(200) DEFAULT 'SELECT * FROM MyTemporaryTable WHERE col1 = 1';
    DECLARE intermediate_result INTEGER;
    DECLARE procedure_result INTEGER DEFAULT 0;
    DECLARE C2 CURSOR FOR SELECT col1 FROM MyTemporaryTable WHERE col1 = 1;

    -- Actual Cursor usage
    DECLARE C1 CURSOR FOR S1;
    PREPARE S1 FROM SQL_string_sel;
    OPEN C1;
    FETCH C1 INTO intermediate_result;
    CLOSE C1;
    SET procedure_result = intermediate_result;
    INSERT INTO databaseTest.MyStatusTable(Col1) VALUES (procedure_result);

    OPEN C2;
    FETCH C2 INTO intermediate_result;
    CLOSE C2;
    SET procedure_result = procedure_result + intermediate_result;
END;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;
Copy

Col1

1

Snowflake Scripting

Note

Usages for cursors must be renamed and declared again.

 CREATE OR REPLACE PROCEDURE fetch_cursor_ignored_query_cursor ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ ""origin"": ""sf_sc"", ""name"": ""snowconvert"", ""version"": {  ""major"": 0,  ""minor"": 0,  ""patch"": ""0"" }, ""attributes"": {  ""component"": ""none"",  ""convertedOn"": ""01/01/0001"" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    --Variables for the example's procedure_results
    SQL_string_sel VARCHAR(200) DEFAULT 'SELECT
   * FROM
   MyTemporaryTable
WHERE col1 = 1';
    intermediate_result INTEGER;
    procedure_result INTEGER DEFAULT 0;
    S1 RESULTSET;
    prepareQuery_aux_sql VARCHAR;
  BEGIN
     
    -- Actual Cursor usage
    LET C2 CURSOR
    FOR
      SELECT
        col1
      FROM
        MyTemporaryTable
      WHERE
        col1 = 1;
    prepareQuery_aux_sql := SQL_string_sel;
    S1 := (
      EXECUTE IMMEDIATE prepareQuery_aux_sql
    );
    LET CURSOR_S1_INSTANCE_V0 CURSOR
    FOR
      S1;
    OPEN CURSOR_S1_INSTANCE_V0;
    FETCH
      CURSOR_S1_INSTANCE_V0
    INTO
      intermediate_result;
    CLOSE CURSOR_S1_INSTANCE_V0;
    procedure_result := intermediate_result;
    INSERT INTO databaseTest.MyStatusTable (Col1)
    VALUES (procedure_result);
    OPEN C2;
    FETCH
      C2
    INTO
      intermediate_result;
    CLOSE C2;
    procedure_result := procedure_result + intermediate_result;
  END;
$$;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;
Copy

Col1

1

Prepare combined with nested cursors

Teradata
 REPLACE PROCEDURE fetch_nested_cursor()
BEGIN
    --Variables for the example's procedure_results
    DECLARE SQL_string_sel VARCHAR(200) DEFAULT 'SELECT col1 FROM MyTemporaryTable WHERE col1 = 1';
    DECLARE intermediate_result INTEGER;
    DECLARE C2 CURSOR FOR SELECT col1 FROM MyTemporaryTable WHERE col1 = 1;

    -- Actual Cursor usage
    DECLARE C1 CURSOR FOR S1;
    PREPARE S1 FROM SQL_string_sel;
    OPEN C1;
    OPEN C2;
    FETCH C2 INTO intermediate_result;

    CLOSE C2;
    FETCH C1 INTO intermediate_result;
    CLOSE C1;
END;
Copy
No returning information.

Copy
Snowflake Scripting

Note

Usages for cursors must be renamed and declared again.

 CREATE OR REPLACE PROCEDURE fetch_nested_cursor ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ ""origin"": ""sf_sc"", ""name"": ""snowconvert"", ""version"": {  ""major"": 0,  ""minor"": 0,  ""patch"": ""0"" }, ""attributes"": {  ""component"": ""none"",  ""convertedOn"": ""01/01/0001"" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    --Variables for the example's procedure_results
    SQL_string_sel VARCHAR(200) DEFAULT 'SELECT
   col1 FROM
   MyTemporaryTable
WHERE col1 = 1';
    intermediate_result INTEGER;
    S1 RESULTSET;
    prepareQuery_aux_sql VARCHAR;
  BEGIN
     
     
    -- Actual Cursor usage
    LET C2 CURSOR
    FOR
      SELECT
        col1
      FROM
        MyTemporaryTable
      WHERE
        col1 = 1;
    prepareQuery_aux_sql := SQL_string_sel;
    S1 := (
      EXECUTE IMMEDIATE prepareQuery_aux_sql
    );
    LET CURSOR_S1_INSTANCE_V0 CURSOR
    FOR
      S1;
    OPEN CURSOR_S1_INSTANCE_V0;
    OPEN C2;
    FETCH
      C2
    INTO
      intermediate_result;
    CLOSE C2;
    FETCH
      CURSOR_S1_INSTANCE_V0
    INTO
      intermediate_result;
    CLOSE CURSOR_S1_INSTANCE_V0;
  END;
$$;
Copy
No returning information.

Copy

Variable markers without variable reordering

Warning

This case is not supported yet.

Teradata
 CREATE PROCEDURE PREPARE_ST_TEST()
BEGIN
    DECLARE ctry_list VARCHAR(100);
    DECLARE SQL_string_sel VARCHAR(255);
    DECLARE col_value NUMBER;

    DECLARE C1 CURSOR FOR S1;

    SET ctry_list = '';
    SET col_value = 1;
    SET SQL_string_sel = 'SELECT * FROM databaseTest.MyTemporaryTable where Col1 = ?';
    PREPARE S1 FROM SQL_string_sel;
    OPEN C1 USING col_value;
    FETCH C1 INTO ctry_list;
    IF (ctry_list <> '') THEN
        INSERT INTO databaseTest.MyStatusTable(col1) VALUES ('ok');
    END IF;
    CLOSE C1;
END;

CALL PREPARE_ST_TEST();
SELECT * FROM MyStatusTable;
Copy

Col1

ok

Snowflake Scripting

Note

Usages for cursors must be renamed and declared again.

 CREATE OR REPLACE PROCEDURE PREPARE_ST_TEST_MARKERS ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE 
        p1 RESULTSET;
        p1_sql VARCHAR DEFAULT '';
        
    BEGIN
        LET ctry_list VARCHAR(100);
        LET SQL_string_sel VARCHAR(255);
        LET col_value NUMBER(38, 18);
        LET S1 RESULTSET;
        
        ctry_list := '';
        
        col_value := 1;
        
        SQL_string_sel := 'SELECT * FROM MyTemporaryTable WHERE Col1 = ?';
        
        p1_sql := SQL_string_sel;
        S1 := (
            EXECUTE IMMEDIATE p1_sql USING (col_value)
        );
        LET C1 CURSOR FOR S1;
        
        OPEN C1;
            FETCH C1 INTO ctry_list;
        IF (RTRIM(ctry_list) <> '') THEN
            INSERT INTO MyStatusTable (col1)
            VALUES ('ok');
        END IF;
            CLOSE C1;
    END;
$$;
Copy

Col1

ok

Variable markers with variable reordering

Warning

This case is not supported yet.

Note

When there are variables setting the value into different ones between the PREPARE statement and OPEN cursor in Teradata, It is necessary to move this variable before the EXECUTE IMMEDIATE in Snowflake. So, the dynamic variable information is updated at the moment of running the dynamic query.

Teradata
 CREATE PROCEDURE PREPARE_ST_TEST()
BEGIN
    DECLARE ctry_list VARCHAR(100);
    DECLARE SQL_string_sel VARCHAR(255);
    DECLARE col_name NUMBER;

    DECLARE C1 CURSOR FOR S1;

    SET ctry_list = '';
    SET col_name = 1;
    SET SQL_string_sel = 'SELECT * FROM databaseTest.MyTemporaryTable where Col1 = ?';
    PREPARE S1 FROM SQL_string_sel;
    SET col_name = 2; // change value before open cursor
    OPEN C1 USING col_name;
    FETCH C1 INTO ctry_list;
    IF (ctry_list <> '') THEN
        INSERT INTO databaseTest.MyStatusTable(col1) VALUES ('ok');
    END IF;
    CLOSE C1;
END;

CALL PREPARE_ST_TEST();
SELECT * FROM MyStatusTable;
Copy
"MyStatusTable" should be empty.

Copy
Snowflake Scripting

Note

Usages for cursors must be renamed and declared again.

 CREATE OR REPLACE PROCEDURE PREPARE_ST_TEST_MARKERS ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE 
        p1 RESULTSET;
        p1_sql VARCHAR DEFAULT '';
        
    BEGIN
        LET ctry_list VARCHAR(100);
        LET SQL_string_sel VARCHAR(255);
        LET col_value NUMBER(38, 18);
        LET S1 RESULTSET;
        
        ctry_list := '';
        
        col_value := 1;
        
        SQL_string_sel := 'SELECT * FROM MyTemporaryTable WHERE Col1 = ?';
        
        p1_sql := SQL_string_sel;

        col_value:= 2; // Move variable setting before the EXECUTE IMMEDIATE
        
        S1 := (
            EXECUTE IMMEDIATE p1_sql USING (col_value)
        );
                
        LET C1 CURSOR FOR S1;
        
        OPEN C1;
            FETCH C1 INTO ctry_list;
        IF (RTRIM(ctry_list) <> '') THEN
            INSERT INTO MyStatusTable (col1)
            VALUES ('ok');
        END IF;
            CLOSE C1;
    END;
$$;

CALL PREPARE_ST_TEST();
SELECT * FROM MyStatusTable;
Copy
"MyStatusTable" should be empty.

Copy

Anonymous blocks - Declaration outside the block

Warning

This case is not supported yet.

Teradata
 REPLACE PROCEDURE anonymous_blocks_case(OUT procedure_result INTEGER)
BEGIN
    --Variables for the example's procedure_results
    DECLARE SQL_string_sel VARCHAR(200) DEFAULT 'SELECT col1 FROM MyTemporaryTable WHERE col1 = 1';

    -- Actual Cursor usage
    DECLARE C1 CURSOR FOR S1;
    DECLARE C2 CURSOR FOR S2;

    PREPARE S1 FROM SQL_string_sel;
    OPEN C1;
    FETCH C1 INTO procedure_result;
    CLOSE C1;

    BEGIN
        PREPARE S2 FROM SQL_string_sel;
        OPEN C2;
        FETCH C2 INTO procedure_result;
        CLOSE C2;
    END;

    OPEN C1;
    CLOSE C1;
END;
Copy
No returning information.

Copy
 CREATE OR REPLACE PROCEDURE anonymous_blocks_case (
--                                                   OUT
                                                       PROCEDURE_RESULT INTEGER)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "none",  "convertedOn": "01/01/0001" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    --Variables for the example's procedure_results
    SQL_string_sel VARCHAR(200) DEFAULT 'SELECT
   col1 FROM
   MyTemporaryTable
WHERE col1 = 1';
    S1 RESULTSET;
    prepareQuery_aux_sql VARCHAR;
    S2 RESULTSET;
  BEGIN
    -- Actual Cursor usage
     
    prepareQuery_aux_sql := SQL_string_sel
    S1 := (
      EXECUTE IMMEDIATE prepareQuery_aux_sql
    );
    LET CURSOR_S1_INSTANCE_V0 CURSOR
    FOR
      S1;
    OPEN CURSOR_S1_INSTANCE_V0;
    FETCH
      CURSOR_S1_INSTANCE_V0
    INTO
      procedure_result;
    CLOSE CURSOR_S1_INSTANCE_V0;
 
    BEGIN
      prepareQuery_aux_sql := SQL_string_sel
      S2 := (
        EXECUTE IMMEDIATE prepareQuery_aux_sql
      );
      LET CURSOR_S2_INSTANCE_V# CURSOR
      FOR
        S1;
      OPEN CURSOR_S2_INSTANCE_V#;
      FETCH
        CURSOR_S2_INSTANCE_V#
      INTO
        procedure_result;
      CLOSE CURSOR_S2_INSTANCE_V#;
    END;
    
    OPEN CURSOR_S1_INSTANCE_V0; -- NAME REMAINS AS NEEDED IN LOGIC
    CLOSE CURSOR_S1_INSTANCE_V0;
    RETURN null;
  END;
$$;
Copy
No returning information.

Copy

Known Issues

  • Review carefully nested cursors and conditionals, if that is the case.

Related EWIs

No related EWIs.

REPEAT

Note

Some parts in the output code are omitted for clarity reasons.

Description

Teradata’s REPEAT statement is translated to Snowflake Scripting REPEAT syntax.

For more information on Teradata Repeat, check here.

 [label_name:] REPEAT 
    { sql_statement }
    UNTIL conditional_expression
END REPEAT [label_name];
Copy

Sample Source Patterns

Teradata

 CREATE PROCEDURE repeatProcedure(OUT resultCounter INTEGER)
BEGIN
    DECLARE counter INTEGER DEFAULT 0;
   
    customeLabel: REPEAT 
    	SET counter = counter + 1;
	UNTIL 10 < counter    
    END REPEAT customeLabel;
   
    SET resultCounter = counter;
END;

CALL repeatProcedure(:?);
Copy
|resultCounter|
|-------------|
|11           |

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE repeatProcedure (
--                                             OUT
                                                 RESULTCOUNTER INTEGER)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		counter INTEGER DEFAULT 0;
	BEGIN
		 
		REPEAT
			counter := counter + 1;
		UNTIL (10 < counter)
		END REPEAT CUSTOMELABEL;
		resultCounter := counter;
		RETURN resultCounter;
	END;
$$;

CALL repeatProcedure(:?);
Copy
|REPEATPROCEDURE|
|---------------|
|1             |

Copy

Known Issues

No issues were found.

Related EWIs

No related EWIs.

SET

Note

Some parts in the output code are omitted for clarity reasons.

Description

Assigns a value to a local variable or parameter in a stored procedure.

For more information regarding Teradata SET, check here.

 SET assigment_target = assigment_source ;
Copy

Sample Source Patterns

Teradata

 CREATE PROCEDURE setExample ( OUT PARAM1 INTEGER )
BEGIN
    DECLARE COL_COUNT INTEGER;
    SET COL_COUNT = 3;
    SET PARAM1 = COL_COUNT + 1;
END;
Copy
|PARAM1 |
|-------|
|4      |

Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE setExample (
--                                         OUT
                                             PARAM1 INTEGER )
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        COL_COUNT INTEGER;
    BEGIN
         
        COL_COUNT := 3;
        PARAM1 := COL_COUNT + 1;
        RETURN PARAM1;
    END;
$$;
Copy
|PARAM1 |
|-------|
|4      |

Copy

Known Issues

No issues were found.

Related EWIs

No related EWIs.

SYSTEM_DEFINED

Note

Some parts in the output code are omitted for clarity reasons.

Note

Non-relevant statement.

Warning

Notice that this statement isremoved from the migrationbecause it is a non-relevant syntax. It means that it is not required in Snowflake.

Description

Property in Teradata that can be after a CREATE statement in cases such as JOIN INDEX.

Sample Source Patterns

Notice that SYSTEM_DEFINED has been removed from the source code because it is a non-relevant syntax in Snowflake.

 CREATE SYSTEM_DEFINED JOIN INDEX MY_TESTS.MYPARTS_TJI004 ,FALLBACK ,CHECKSUM = DEFAULT, MAP = TD_MAP1 AS
CURRENT TRANSACTIONTIME 
SELECT
    MY_TESTS.myParts.ROWID,
    MY_TESTS.myParts.part_id,
    MY_TESTS.part_duration
FROM MY_TESTS.myParts
UNIQUE PRIMARY INDEX (part_id);
Copy
 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TESTS.myParts" **
CREATE OR REPLACE DYNAMIC TABLE MY_TESTS.MYPARTS_TJI004
--** SSC-FDM-0031 - DYNAMIC TABLE REQUIRED PARAMETERS SET BY DEFAULT **
TARGET_LAG='1 day'
WAREHOUSE=UPDATE_DUMMY_WAREHOUSE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/01/2024" }}'
AS
--    --** SSC-FDM-TD0025 - TEMPORAL FORMS ARE NOT SUPPORTED IN SNOWFLAKE **
--    CURRENT TRANSACTIONTIME
                            SELECT
        MY_TESTS.myParts.ROWID,
        MY_TESTS.myParts.part_id,
        MY_TESTS.part_duration
    FROM
        MY_TESTS.myParts;
Copy

Known Issues

No issues were found.

Related EWIs

  1. SSC-FDM-0007: Element with missing dependencies.

  2. SSC-FDM-TD0025: Teradata Database Temporal Table is not supported in Snowflake.

  3. SSC-FDM-0031: Dynamic Table required parameters set by default

WHILE

Note

Some parts in the output code are omitted for clarity reasons.

Description

Teradata’s WHILE statement is translated to Snowflake ScriptingWHILE syntax.

For more information on Teradata While, check here.

 [label_name:] WHILE conditional_expression DO
    { sql_statement }
END WHILE [label_name];
Copy

Sample Source Patterns

Teradata

 REPLACE PROCEDURE whileProcedure(OUT resultCounter INTEGER)
BEGIN
    DECLARE counter INTEGER DEFAULT 0;
    customeLabel: WHILE counter < 10 DO
        SET counter = counter + 1;
    END WHILE customeLabel;
    SET resultCounter = counter;
END;

CALL whileProcedure(:?);
Copy
 |resultCounter|
|-------------|
|10           |
Copy
Snowflake Scripting
 CREATE OR REPLACE PROCEDURE whileProcedure (
--                                            OUT
                                                RESULTCOUNTER INTEGER)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        counter INTEGER DEFAULT 0;
    BEGIN
         
        WHILE ( counter < 10) LOOP
            counter := counter + 1;
        END LOOP CUSTOMELABEL;
        resultCounter := counter;
        RETURN resultCounter;
    END;
$$;

CALL whileProcedure(:?);
Copy
 |WHILEPROCEDURE|
|--------------|
|10            |
Copy

Known Issues

No issues were found.

Related EWIs

No related EWIs.