```
### Examples
Replace RETURN value parameter with standard OUTPUT parameters.
```
CREATE PROCEDURE ProcessImportBatch()
IN @BatchID INT, OUT @ErrorNumber INT
BEGIN
CALL Step1 (@BatchID)
CALL Step2 (@BatchID)
CALL Step3 (@BatchID)
IF error_count > 1
SET @ErrorNumber = -1 -- indicate special condition
END
```
Use a `LOOP` cursor with a source table to replace table valued parameters.
Create the `OrderItems` table.
```
CREATE TABLE OrderItems
(
OrderID INT NOT NULL,
Item VARCHAR(20) NOT NULL,
Quantity SMALLINT NOT NULL,
PRIMARY KEY(OrderID, Item)
);
```
Create and populate `SourceTable` as a temporary data store for incoming rows.
```
CREATE TABLE SourceTable
(
OrderID INT,
Item VARCHAR(20),
Quantity SMALLINT,
PRIMARY KEY (OrderID, Item)
);
```
```
INSERT INTO SourceTable (OrderID, Item, Quantity)
VALUES
(1, 'M8 Bolt', 100),
(2, 'M8 Nut', 100),
(3, 'M8 Washer', 200);
```
Create a procedure to loop through all rows in `SourceTable` and insert them into the `OrderItems` table.
```
CREATE PROCEDURE LoopItems()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE var_OrderID INT;
DECLARE var_Item VARCHAR(20);
DECLARE var_Quantity SMALLINT;
DECLARE ItemCursor CURSOR
FOR SELECT OrderID,
Item,
Quantity
FROM SourceTable;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET done = TRUE;
OPEN ItemCursor;
CursorStart: LOOP
FETCH NEXT FROM ItemCursor
INTO var_OrderID, var_Item, var_Quantity;
IF Done THEN LEAVE CursorStart;
END IF;
INSERT INTO OrderItems (OrderID, Item, Quantity)
VALUES (var_OrderID, var_Item, var_Quantity);
END LOOP;
CLOSE ItemCursor;
END;
```
Call the stored procedure.
```
CALL LoopItems();
```
Select all rows from the OrderItems table.
```
SELECT * FROM OrderItems;
```
For the preceding example, the result looks as shown following.
```
OrderID Item Quantity
1 M8 Bolt 100
2 M8 Nut 100
3 M8 Washer 200
```
## Summary
The following table summarizes the differences between MySQL Stored Procedures and SQL Server Stored Procedures.
| Feature | SQL Server | Aurora MySQL | Workaround |
| --- | --- | --- | --- |
| General `CREATE` syntax differences | CREATE PROC|PROCEDURE
@Parameter1 ,
...n
AS
| CREATE PROCEDURE
(Parameter1
,...n)
| Rewrite stored procedure creation scripts to use `PROCEDURE` instead of `PROC`. Rewrite stored procedure creation scripts to omit the `AS` keyword. Rewrite stored procedure parameters to not use the `@` symbol in parameter names. Add parentheses around the parameter declaration. Rewrite stored procedure parameter direction `OUTPUT` to `OUT` or `INOUT` for bidirectional parameters. `IN` is the parameter direction for both MySQL and SQL Server. |
| Security context | { EXEC | EXECUTE } AS
{ CALLER | SELF | OWNER |
'user_name' } | DEFINER = 'user' |
CURRENT_USER
in conjunction with SQL SECURITY {
DEFINER | INVOKER } | For stored procedures that use an explicit user name, rewrite the code from `EXECUTE AS 'user'` to `DEFINER = 'user'` and `SQL SECURITY DEFINER`. For stored procedures that use the `CALLER` option, rewrite the code to include `SQL SECURITY INVOKER`. For stored procedures that use the `SELF` option, rewrite the code to `DEFINER = CURRENT_USER` and `SQL SECURITY DEFINER`. Unlike SQL Server, `OWNER`s can’t be specified and must be explicitly named. |
| Encryption | Use the `WITH ENCRYPTION` option. | Not supported in Aurora MySQL. | |
| Parameter direction | `IN` and `OUT\|OUTPUT`, by default `OUT` can be used as `IN` as well. | `IN`, `OUT`, and `INOUT` | Although the functionality of these parameters is the same for SQL Server and MySQL, make sure that you rewrite the code for syntax compliance. Use `OUT` instead of `OUTPUT`. Use `INOUT` instead of `OUT` for bidirectional parameters. |
| Recompile | Use the `WITH RECOMPILE` option. | Not supported in Aurora MySQL. | |
| Table-valued parameters | Use declared table type user-defined parameters. | Not supported in Aurora MySQL. | See the preceding example for a workaround. |
| `INSERT… EXEC` | Use the output of the stored procedure as input to an `INSERT` statement. | Not supported in Aurora MySQL. | Use tables to hold the data or pass string parameters formatted as CSV, XML, JSON (or any other convenient format) and then parse the parameters before the `INSERT` statement. |
| Additional restrictions | Use `BULK INSERT` to load data from text file. | The LOAD DATA statement isn’t allowed in stored procedures. | |
| `RETURN` value | `RETURN ` | Not supported. | Use a standard `OUTPUT` parameter instead. |
For more information, see [Stored Procedures and Functions](https://dev.mysql.com/doc/refman/5.7/en/faqs-stored-procs.html) and [CREATE PROCEDURE and CREATE FUNCTION Statements](https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html) in the *MySQL documentation*.