28 Jan 21 ·  Cpaul in Guides ·        Bookmark ·  Report ·  More actions.. Lock comments ·  Pin thread

Convert Teradata Stored Procedure to BigQuery - Migration Guide

Stored Procedure Declaration

TeradataBigQuery Equivalent
CREATE PROCEDURE
CREATE PROCEDURE
REPLACE PROCEDURE
CREATE OR REPLACE PROCEDURE
CALLCALL

Variable declaration and assignment

TeradataBigQuery Equivalent
DECLAREDECLARE
SETSET

BigQuery Stored Procedure Example

CREATE OR REPLACE PROCEDURE Dataset.Procedurename()
BEGIN
insert into dataset.tablename
select * from dataset.table2;
END

Flow Control Statements

TeradataBigQuery Equivalent
IF condition THEN stmts ELSE stmts END IF
Supported
label: LOOP stmts END LOOP label;
GOTO-style block constructs are not Supported
REPEAT stmts UNTIL condition END REPEAT;
WHILE condition DO stmts END WHILE
LEAVE label;
LEAVE is not used for GOTO-style blocks; it is used as a synonym for BREAK to leave a WHILE loop
WITH RECURSIVE temp_table AS ( ... );
Not Supported
FOR var AS SELECT ... DO stmts END FOR;
Not Supported
FOR var AS cur CURSOR FOR SELECT ... DO stmts END FOR;
Not Supported

Dynamic SQL 

TeradataBigQuery Equivalent
EXECUTE IMMEDIATE sql_str;
EXECUTE IMMEDIATE sql_str;
EXECUTE stmt_id [USING var,...];
EXECUTE IMMEDIATE stmt_id USING var;
PREPARE stmt_id FROM sql_str;
Not Supported

Cursor & Loops

Cursors and the following constructs are not supported in BigQuery

  • DECLARE cursor_name CURSOR [FOR | WITH] ...
  • PREPARE stmt_id FROM sql_str;
  • OPEN cursor_name [USING var, ...];
  • FETCH cursor_name INTO var, ...;
  • CLOSE cursor_name;

Error Handling

TeradataBigQuery Equivalent
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN ... EXCEPTION WHEN ERROR THEN
SIGNAL sqlstate
RAISE message
DECLARE CONTINUE HANDLER FOR SQLSTATE VALUE 23505;
Not Supported

Although, Stored Procedures conversion is not supported by Roboquery. It can convert the SQL that is present inside the Stored Procedure