Flow control for T-SQL
This topic provides reference information about flow control in SQL Server and Amazon Aurora MySQL, comparing their respective capabilities and syntax differences. You can use this guide to understand how to adapt your existing SQL Server flow control statements when migrating to Aurora MySQL.
Feature compatibility | Amazon SCT / Amazon DMS automation level | Amazon SCT action code index | Key differences |
---|---|---|---|
|
|
Syntax and option differences, similar functionality. |
SQL Server Usage
Although SQL is a mostly declarative language, it does support flow control commands, which provide run time dynamic changes in script run paths.
Note
Before SQL/PSM was introduced in SQL:1999, the ANSI standard did not include flow control constructs. Therefore, there are significant syntax differences among RDBMS engines.
SQL Server provides the following flow control keywords.
-
BEGIN… END
— Define boundaries for a block of commands that run together. -
RETURN
— Exit a server code module such as stored procedure, function, and so on and return control to the calling scope. You can useRETURN <value>
to return anINT
value to the calling scope. -
BREAK
— ExitWHILE
loop run. -
THROW
— Raise errors and potentially return control to the calling stack. -
CONTINUE
— Restart aWHILE
loop. -
TRY… CATCH
— Error handling. For more information, see Error Handling. -
GOTO label
— Moves the run point to the location of the specified label. -
WAITFOR
— Delay. -
IF… ELSE
— Conditional flow control. -
WHILE <condition>
— Continue looping while<condition>
returnsTRUE
.Note
WHILE
loops are commonly used with cursors and use the system variable@@FETCH_STATUS
to determine when to exit. For more information, see Cursors.
For more information, see Error Handling.
Examples
Create and populate the OrderItems
table.
CREATE TABLE OrderItems ( OrderID INT NOT NULL, Item VARCHAR(20) NOT NULL, Quantity SMALLINT NOT NULL, PRIMARY KEY(OrderID, Item) );
INSERT INTO OrderItems (OrderID, Item, Quantity) VALUES (1, 'M8 Bolt', 100), (2, 'M8 Nut', 100), (3, 'M8 Washer', 200);
WAITFOR
Use WAITFOR
to introduce a one minute delay between background batches purging old data.
SET ROWCOUNT 1000; WHILE @@ROWCOUNT > 0; BEGIN; DELETE FROM OrderItems WHERE OrderDate < '19900101'; WAITFOR DELAY '00:01:00'; END;
GOTO
Use GOTO
to skip a code section based on an input parameter in a stored procedure.
CREATE PROCEDURE ProcessOrderItems @OrderID INT, @Item VARCHAR(20), @Quantity INT, @UpdateInventory BIT AS BEGIN INSERT INTO OrderItems (OrderID, Item, Quantity) SELECT @OrderID, @item, @Quantity IF @UpdateInventory = 0 GOTO Finish UPDATE Inventory SET Stock = Stock - @Quantity WHERE Item = @Item /* Additional Inventory Processing */ finish: /* Generate Results Log*/ END
Dynamic Procedure Run Path
The following example demonstrates a solution for running different processes based on the number of items in an order.
Declare a cursor for looping through all OrderItems and calculating the total quantity for each order.
DECLARE OrderItemCursor CURSOR FAST_FORWARD FOR SELECT OrderID, SUM(Quantity) AS NumItems FROM OrderItems GROUP BY OrderID ORDER BY OrderID; DECLARE @OrderID INT, @NumItems INT; -- Instantiate the cursor and loop through all orders. OPEN OrderItemCursor; FETCH NEXT FROM OrderItemCursor INTO @OrderID, @NumItems WHILE @@Fetch_Status = 0 BEGIN; IF @NumItems > 100 PRINT 'EXECUTING LogLargeOrder - ' + CAST(@OrderID AS VARCHAR(5)) + ' ' + CAST(@NumItems AS VARCHAR(5)); ELSE PRINT 'EXECUTING LogSmallOrder - ' + CAST(@OrderID AS VARCHAR(5)) + ' ' + CAST(@NumItems AS VARCHAR(5)); FETCH NEXT FROM OrderItemCursor INTO @OrderID, @NumItems; END; -- Close and deallocate the cursor. CLOSE OrderItemCursor; DEALLOCATE OrderItemCursor;
For the preceding example, the result looks as shown following.
EXECUTING LogSmallOrder - 1 100 EXECUTING LogSmallOrder - 2 100 EXECUTING LogLargeOrder - 3 200
For more information, see Control-of-Flow
MySQL Usage
Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) provides the following flow control constructs:
-
BEGIN… END
— Define boundaries for a block of commands that are ran together. -
CASE
— Run a set of commands based on a predicate (not to be confused withCASE
expressions). -
IF… ELSE
— Conditional flow control. -
ITERATE
— Restart aLOOP
,REPEAT
, andWHILE
statement. -
LEAVE
— Exit a server code module such as stored procedure, function, and so on, and return control to the calling scope. -
LOOP
— Loop indefinitely. -
REPEAT… UNTIL
— Loop until the predicate is true. -
RETURN
— Terminate the run of the current scope and return to the calling scope. -
WHILE
— Continue looping while the condition returnsTRUE
. -
SLEEP
— Pause the run for a specified number of seconds.
Examples
Create and populate the OrderItems
table.
CREATE TABLE OrderItems ( OrderID INT NOT NULL, Item VARCHAR(20) NOT NULL, Quantity SMALLINT NOT NULL, PRIMARY KEY(OrderID, Item) );
INSERT INTO OrderItems (OrderID, Item, Quantity) VALUES (1, 'M8 Bolt', 100), (2, 'M8 Nut', 100), (3, 'M8 Washer', 200);
Rewrite of SQL Server WAITFOR
delay using SLEEP
.
CREATE PROCEDURE P() BEGIN DECLARE RR INT; SET RR = ( SELECT COUNT(*) FROM OrderItems WHERE OrderDate < '19900101' ); WHILE RR > 0 DO DELETE FROM OrderItems WHERE OrderDate < '19900101'; DO SLEEP (60); SET RR = ( SELECT COUNT(*) FROM OrderItems WHERE OrderDate < '19900101' ); END WHILE; END;
Rewrite of SQL Server GOTO
using nested blocks.
CREATE PROCEDURE ProcessOrderItems (Var_OrderID INT, Var_Item VARCHAR(20), Var_Quantity INT, UpdateInventory BIT) BEGIN INSERT INTO OrderItems (OrderID, Item, Quantity) VALUES(Var_OrderID, Var_Item, Var_Quantity) IF @UpdateInventory = 1 BEGIN UPDATE Inventory SET Stock = Stock - @Quantity WHERE Item = @Item /* Additional Inventory Processing...*/ END /* Generate Results Log */ END
Dynamic Procedure Run Path
The following example demonstrates a solution for running different processes based on the number of items in an order.
This example provides the same functionality as the example for SQL Server flow control. However, unlike the SQL Server example which you run as a batch script, Aurora MySQL variables can only be used in stored routines such as procedures and functions.
Create a procedure to declare a cursor and loop through the order items.
CREATE PROCEDURE P() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE var_OrderID INT; DECLARE var_NumItems INT; DECLARE OrderItemCursor CURSOR FOR SELECT OrderID, SUM(Quantity) AS NumItems FROM OrderItems GROUP BY OrderID ORDER BY OrderID; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN OrderItemCursor; CursorStart: LOOP FETCH NEXT FROM OrderItemCursor INTO var_OrderID, var_NumItems; IF done THEN LEAVE CursorStart; END IF; IF var_NumItems > 100 THEN SELECT CONCAT('EXECUTING LogLargeOrder - ', CAST(var_OrderID AS VARCHAR(5)),' Num Items: ', CAST(var_ NumItems AS VARCHAR(5))) ELSE SELECT CONCAT('EXECUTING LogSmallOrder - ', CAST(var_OrderID AS VARCHAR(5)), ' Num Items: ', CAST(var_NumItems AS VARCHAR(5))) END IF; END LOOP; CLOSE OrderItemCursor; END;
Summary
While there are some syntax differences between SQL Server and Aurora MySQL flow control statements, most rewrites should be straightforward. The following table summarizes the differences and identifies how to modify T-SQL code to support similar functionality in Aurora MySQL.
Feature | SQL Server | Aurora MySQL | Workaround |
---|---|---|---|
|
Define command block boundaries. |
Define command block boundaries. |
Compatible. |
|
Exit the current scope and return to caller. Supported for both scripts and stored code such as procedures and functions. |
Exit a stored function and return to caller. |
For Aurora MySQL, Rewrite the T-SQL code using the The You can’t |
|
Exit the |
Not supported. |
Rewrite the logic to explicitly set a value that will render the |
|
Raise errors and potentially return control to the calling stack. |
Errors are handled by |
For more information, see Error Handling. |
|
Error handling |
Errors are handled by |
For more information, see Error Handling. |
|
Move run to specified label. |
Not supported. |
Consider rewriting the flow logic using either |
|
Delay. |
Not supported. |
Replace
|
|
Conditional flow control. |
Conditional flow control. |
The functionality is compatible, but the syntax differs. SQL Server uses Rewrite T-SQL code to add the mandatory |
|
Continue running while condition is |
Continue running while condition is |
The functionality is compatible, but the syntax differs. SQL Server uses Rewrite T-SQL code to use the Aurora MySQL keywords. |
For more information, see Flow Control Statements