SnowConvert AI - Teradata - SQL to Snowflake Scripting (Procedures)¶

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

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

 REPLACE PROCEDURE procedureBasicAbort()
BEGIN
    ABORT;
    ROLLBACK;
END;

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

 REPLACE PROCEDURE procedureWhereAbort(AnotherValueProc INTEGER)
BEGIN
    ABORT WHERE AValueProc > 2;

    ROLLBACK WHERE (AnotherValueProc > 2);
END;

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

 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;

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

 CREATE TABLE  ReferenceTable
    (ColumnValue INTEGER);

REPLACE PROCEDURE procedureFromTableAbort()
BEGIN
    ROLLBACK WHERE ReferenceTable.ColumnValue > 2;
    ABORT WHERE ReferenceTable.ColumnValue > 4;
END;

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

 ABORT 'Error message for abort';
ROLLBACK  'Error message for rollback';

 ABORT 'Error message for abort';
ROLLBACK  'Error message for rollback';

 ROLLBACK WHERE SUM(ATable.AValue) < 2;
ABORT WHERE SUM(ATable.AValue) < 2;

 ROLLBACK WHERE SUM(ATable.AValue) < 2;
ABORT WHERE SUM(ATable.AValue) < 2;

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

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

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

 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;

 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;

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

 REPLACE PROCEDURE BeginEndProcedure()
BEGIN
    DECLARE HELLOSTRING VARCHAR(60);
    BEGIN TRANSACTION
        SET HELLOSTRING = 'HELLO WORLD';
    END TRANSACTION;
END;

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

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

 REPLACE PROCEDURE BeginEndProcedure()
BEGIN
    DECLARE HELLOSTRING VARCHAR(60);
    BEGIN REQUEST
        SET HELLOSTRING = 'HELLO WORLD';
    END REQUEST;
END;

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

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

 REPLACE PROCEDURE BeginEndProcedure()
BEGIN
    DECLARE HELLOSTRING VARCHAR(60);
    label_name: BEGIN
        SET HELLOSTRING = 'HELLO WORLD';
    END label_name;
END;

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

 REPLACE PROCEDURE procedureLabelSingle()
label_name: BEGIN
    DECLARE HELLOSTRING VARCHAR(60);
    SET HELLOSTRING = 'HELLO WORLD';
END label_name;

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

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

 CREATE TABLE case_table(col varchar(30));

 CREATE OR REPLACE TABLE case_table (
col varchar(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"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;

 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;

 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;

CREATE OR REPLACE PROCEDURE caseExample2 (GRADE NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        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;

 DECLARE cursor_name [ SCROLL | NO SCROLL ] CURSOR
     [
          WITHOUT RETURN
          |
          WITH RETURN [ ONLY ] [ TO [ CALLER | CLIENT ] ]
     ]
     FOR
     cursor_specification [ FOR [ READ ONLY | UPDATE ] ]
     |
     statement_name
;

 FETCH [ [ NEXT | FIRST ] FROM ] cursor_name INTO
    [ variable_name | parameter_name ] [ ,...n ]
;

 OPEN cursor_name
    [ USING [ SQL_identifier | SQL_paramenter ] [ ,...n ] ]
;

 CLOSE cursor_name ;

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

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

 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;

 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;

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

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

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

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "SampleTable2", "SampleTable1" **
CREATE OR REPLACE PROCEDURE spTwoOrMore ()
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		tbl_result_set VARCHAR;
		tbl_result_set2 VARCHAR;
		return_arr ARRAY := array_construct();
	BEGIN
		tbl_result_set := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
		CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_result_set) AS
			SELECT
				* FROM
				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 return_arr;
	END;
$$;

CALL spTwoOrMore();

 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;

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "TEST_TABLE" **
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/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      LET cursorExample CURSOR
      FOR
         SELECT
            * FROM
            TEST_TABLE
           	WHERE ColumnA = ?
            AND ColumnB ILIKE ?
            and ColumnC ILIKE ?;
      OPEN cursorExample USING (param1, param2, param3);
      RETURN TABLE(resultset_from_cursor(cursorExample));
   END;
$$;

 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;

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "TEST_TABLE", "ResTable" **
CREATE OR REPLACE PROCEDURE TestProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        !!!RESOLVE EWI!!! /*** SSC-EWI-0110 - TRANSFORMATION NOT PERFORMED DUE TO MISSING DEPENDENCIES ***/!!!
        temp_fUsgClass_columnA;
    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
            temp_fUsgClass_columnA := fUsgClass.columnA;
            INSERT INTO ResTable (Column1)
            VALUES (:temp_fUsgClass_columnA);
        END FOR;
    END;
$$;

CALL TestProcedure();

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "ResTable" **
SELECT
    * FROM
    ResTable;

 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;

 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;

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

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

  } handler_action_statement ;

 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;

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

 CREATE PROCEDURE declareConditionExample2 ( )
BEGIN
   DECLARE CONTINUE HANDLER FOR SQLSTATE 'UNSUPPORTED'
     BEGIN
       SET vCHAR_SQLSTATE = SQLCODE;
       SET vSUCCESS    = SQLCODE;
    END;
END;

:force:
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;
$$;

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

 CREATE PROCEDURE declareConditionExample ( )
BEGIN
    DECLARE DB_ERROR CONDITION;
    ...
END;

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

 CREATE PROCEDURE declareConditionExample2 ( )
BEGIN
    DECLARE ERROR_EXISTS CONDITION FOR SQLSTATE VALUE '42000';
END;

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

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

 CREATE PROCEDURE declareExample ( )
BEGIN
    DECLARE COL_NAME, COL_TYPE VARCHAR(200) DEFAULT '' ;
    DECLARE COL_COUNT, COL_LEN INTEGER;
END;

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

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

 CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        INSERT INTO Proc_Error_Table ('procSample', 'Failed SqlException');
    SELECT * FROM Proc_Error_Table;
END;

 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;

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

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

 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;

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

 CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        INSERT INTO Proc_Error_Table ('spSample4', 'Failed SqlException');
    SELECT * FROM Proc_Error_Table;
END;

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

 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;

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

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

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

CREATE MACRO dummyMacro AS(
  SELECT * FROM INVENTORY;
);

 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 AI Helpers Code section is omitted.

  INSERT_TEMP(`SELECT
   *
  FROM
   INVENTORY`,[]);
  return tablelist;
$$;

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

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

 EXECUTE dummyMacro;

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

REPLACE PROCEDURE fetch_cursor_with_using(OUT result INTEGER)
BEGIN
    DECLARE SQL_string VARCHAR(200) DEFAULT 'SELECT col1 FROM MyTable WHERE col1 = ?';
    DECLARE filter_value INTEGER DEFAULT 5;

    DECLARE C1 CURSOR FOR S1;
    PREPARE S1 FROM SQL_string;
    OPEN C1 USING filter_value;
    FETCH C1 INTO result;
    CLOSE C1;
END;