Convert Teradata Stored Procedure to BigQuery - Migration Guide
Stored Procedure Declaration
| Teradata | BigQuery Equivalent |
CREATE PROCEDURE
| CREATE PROCEDURE
|
REPLACE PROCEDURE
| CREATE OR REPLACE PROCEDURE
|
| CALL | CALL |
Variable declaration and assignment
| Teradata | BigQuery Equivalent |
| DECLARE | DECLARE |
| SET | SET |
BigQuery Stored Procedure Example
CREATE OR REPLACE PROCEDURE Dataset.Procedurename()
BEGIN
insert into dataset.tablename
select * from dataset.table2;
END
Flow Control Statements
| Teradata | BigQuery 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
| Teradata | BigQuery 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
| Teradata | BigQuery 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
Post Comment