```
### Examples
CREATE PROC|PROCEDURE, | CREATE PROCEDURE,...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 | DEFINER = 'user' | in conjunction with SQL SECURITY { | 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*.
# Error handling for T-SQL
END TRY
BEGIN CATCH
END CATCH
```
### THROW
, < Error State>] [;]
```
**Examples**
Use `TRY/CATCH` error blocks to handle key violations.
```
CREATE TABLE ErrorTest (Col1 INT NOT NULL PRIMARY KEY);
```
```
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO ErrorTest(Col1) VALUES(1);
INSERT INTO ErrorTest(Col1) VALUES(2);
INSERT INTO ErrorTest(Col1) VALUES(1);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
THROW; -- Throw with no parameters = RETHROW
END CATCH;
```
```
(1 row affected)
(1 row affected)
(0 rows affected)
Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'PK__ErrorTes__A259EE54D8676973'.
Cannot insert duplicate key in object 'dbo.ErrorTest'. The duplicate key value is (1).
```
**Note**
Contrary to what many SQL developers believe, the values 1 and 2 are indeed inserted into `ErrorTestTable` in the preceding example. This behavior is in accordance with ANSI specifications stating that a constraint violation shouldn’t roll back an entire transaction.
Use `THROW` with variables.
```
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO ErrorTest(Col1) VALUES(1);
INSERT INTO ErrorTest(Col1) VALUES(2);
INSERT INTO ErrorTest(Col1) VALUES(1);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
DECLARE @CustomMessage VARCHAR(1000),
@CustomError INT,
@CustomState INT;
SET @CustomMessage = 'My Custom Text ' + ERROR_MESSAGE();
SET @CustomError = 54321;
SET @CustomState = 1;
THROW @CustomError, @CustomMessage, @CustomState;
END CATCH;
```
```
(0 rows affected)
Msg 54321, Level 16, State 1, Line 19
My Custom Text Violation of PRIMARY KEY constraint 'PK__ErrorTes__A259EE545CBDBB9A'.
Cannot insert duplicate key in object 'dbo.ErrorTest'. The duplicate key value is (1).
```
### RAISERROR
| , ,
[WITH option []])
```
**Example**
Raise a custom error.
```
RAISERROR (N'This is a custom error message with severity 10 and state 1.', 10, 1)
```
### FORMATMESSAGE
| , )
```
### Error State Functions
CONDITION
FOR
```
```
= |
```
**Examples**
Declare a condition for MySQL error 1051 (Unknown table error).
```
DECLARE TableDoesNotExist CONDITION FOR 1051;
```
Declare a condition for SQL State 42S02 (Base table or view not found) .
**Note**
This SQLState error corresponds to the MySQL Error 1051.
```
DECLARE TableDoesNotExist CONDITION FOR SQLSTATE VALUE '42S02';
```
### DECLARE … HANDLER
|
|
|
SQLWARNING |
NOT FOUND |
SQLEXCEPTION
```
**Examples**
Declare a handler to ignore warning messages and continue run by assigning an empty statement block.
```
DECLARE CONTINUE HANDLER
FOR SQLWARNING BEGIN END
```
Declare a handler to `EXIT` upon duplicate key violation and log a message to a table.
```
DECLARE EXIT HANDLER
FOR SQLSTATE '23000'
BEGIN
INSERT INTO MyErrorLogTable
VALUES(NOW(), CURRENT_USER(), 'Error 23000')
END
```
### GET DIAGNOSTICS
<@Parameter = CLASS_ORIGIN | SUBCLASS_ORIGIN | RETURNED_
SQLSTATE | MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA |
CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME>
```
**Example**
Retrieve `SQLSTATE` and `MESSAGE_TEXT` from the diagnostic area for the last statement that you ran.
```
GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT
```
### SIGNAL/RESIGNAL
[SET = [,...n]]
= CLASS_ORIGIN | SUBCLASS_ORIGIN | RETURNED_SQLSTATE
| MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_
NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME
```
**Examples**
Raise an explicit error with `SQLSTATE` 55555.
```
SIGNAL SQLSTATE '55555'
```
Re-raise an error with an explicit MySQL error number.
```
RESIGNAL SET MYSQL_ERRNO = 5
```
### Migration Considerations
` to return an `INT` value to the calling scope.
+ `BREAK` — Exit `WHILE` loop run.
+ `THROW` — Raise errors and potentially return control to the calling stack.
+ `CONTINUE` — Restart a `WHILE` loop.
+ `TRY… CATCH` — Error handling. For more information, see [Error Handling](chap-sql-server-aurora-mysql.tsql.errorhandling.md).
+ `GOTO label` — Moves the run point to the location of the specified label.
+ `WAITFOR` — Delay.
+ `IF… ELSE` — Conditional flow control.
+ `WHILE ` — Continue looping while `` returns `TRUE`.
**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](chap-sql-server-aurora-mysql.tsql.cursors.md).
For more information, see [Error Handling](chap-sql-server-aurora-mysql.tsql.errorhandling.md).
### Examples
CALL ` in place of `GOTO`. |
| `WAITFOR` | Delay. | Not supported. | Replace `WAITFOR` with Aurora MySQL `SLEEP`. `SLEEP` is less flexible than `WAITFOR` and only supports delays specified in seconds. Rewrite the code using `SLEEP` to replace `WAITFOR DELAY` and convert the units to seconds. `WAITFOR TIME` isn’t supported in Aurora MySQL. You can calculate the difference in seconds between the desired time and current time using date and rime functions and use the result to dynamically generate the `SLEEP` statement. Alternatively, consider using `CREATE EVENT` with a predefined schedule. |
| `IF… ELSE` | Conditional flow control. | Conditional flow control. | The functionality is compatible, but the syntax differs. SQL Server uses `IF ELSE `. Aurora MySQL uses `IF THEN ELSE ENDIF`. Rewrite T-SQL code to add the mandatory `THEN` and `ENDIF` keywords. |
| `WHILE` | Continue running while condition is `TRUE`. | Continue running while condition is `TRUE`. | The functionality is compatible, but the syntax differs. SQL Server uses `WHILE BEGIN…END`, Aurora MySQL uses `WHILE DO… END WHILE`. Aurora MySQL doesn’t require a `BEGIN…END` block. Rewrite T-SQL code to use the Aurora MySQL keywords. |
For more information, see [Flow Control Statements](https://dev.mysql.com/doc/refman/5.7/en/flow-control-statements.html) in the *MySQL documentation*.
# Full-text search for T-SQL
, 'Populatestatus')` function.
### Examples
)
AGAINST (
[ IN NATURAL LANGUAGE MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| IN BOOLEAN MODE
| WITH QUERY EXPANSION]
)
```
**Note**
The search expression must be constant for all rows searched. Therefore a table column isn’t permitted.
The three types of full-text searches are natural language, Boolean, and query expansion.
### Natural Language Search
Robert
28
James
71
Drapers
```
SQL Server provides native support for XML and JSON in the database using the familiar and convenient T-SQL interface.
### XML Data
My XML Data My XML Data
```
### JSON Data
John Jim John Jim Jack ')
```
```
Results: Jack
```
**Note**
Aurora MySQL doesn’t support MySQL `LOAD XML` syntax. For more information, see [Loading data into an Amazon Aurora MySQL DB cluster from text files in an Amazon S3 bucket](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.LoadFromS3.html) in the *User Guide for Aurora*.
### JSON Data Type
[AS] ]
USING
ON
[WHEN MATCHED [AND ]
THEN UPDATE SET | DELETE]
[WHEN NOT MATCHED [BY TARGET] [AND ]
THEN INSERT [()]
VALUES () | DEFAULT VALUES]
[WHEN NOT MATCHED BY SOURCE [AND ]
THEN UPDATE SET | DELETE]
OUTPUT []
```
### Examples
()
VALUES ()
```
```
REPLACE [INTO]
SET
```
```
REPLACE [INTO] ()
SELECT ...
```
### INSERT … ON DUPLICATE KEY UPDATE
[]
VALUES (
ON DUPLICATE KEY
```
```
INSERT [INTO]
SET
ON DUPLICATE KEY
UPDATE
```
```
INSERT [INTO] []
SELECT ...
ON DUPLICATE KEY
UPDATE
```
### Migration Considerations
WHERE Key NOT IN (SELECT Key FROM Source)` | |
| `WHEN NOT MATCHED BY SOURCE DELETE` | `DELETE FROM Target WHERE KEY NOT IN (SELECT Key FROM Source)` | |
| `OUTPUT` clause | N/A | |
For more information, see [REPLACE Statement](https://dev.mysql.com/doc/refman/5.7/en/replace.html) and [INSERT … ON DUPLICATE KEY UPDATE Statement](https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html) in the *MySQL documentation*.
# PIVOT and UNPIVOT for T-SQL
,
[Pivoted Column 1] AS ,
[Pivoted column 2] AS
...n
FROM
()
AS
PIVOT
(
()
FOR
[]
IN ( [Pivoted Column 1], [Pivoted column 2] ...)
) AS ;
```
#### PIVOT Examples
] .
FOR [ ] . [ ] . [ Schema Name> ] .
```
### Examples
) [PERCENT] [ WITH TIES ]
FROM...
```
### OFFSET… FETCH
[ ASC | DESC ] [ ,...n ]
OFFSET { ROW | ROWS }
[FETCH { FIRST | NEXT } { ROW | ROWS } ONLY ]
```
**Examples**
Create 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),
(3, 'M6 Locking Nut', 300);
```
Retrieve the three most ordered items by quantity.
```
-- Using TOP
SELECT TOP (3) *
FROM OrderItems
ORDER BY Quantity DESC;
-- USING FETCH
SELECT *
FROM OrderItems
ORDER BY Quantity DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
```
```
OrderID Item Quantity
3 M6 Locking Nut 300
3 M8 Washer 200
2 M8 Nut 100
```
Include rows with ties.
```
SELECT TOP (3) WITH TIES *
FROM OrderItems
ORDER BY Quantity DESC;
```
```
OrderID Item Quantity
3 M6 Locking Nut 300
3 M8 Washer 200
2 M8 Nut 100
1 M8 Bolt 100
```
Retrieve half of the rows based on quantity.
```
SELECT TOP (50) PERCENT *
FROM OrderItems
ORDER BY Quantity DESC;
```
```
OrderID Item Quantity
3 M6 Locking Nut 300
3 M8 Washer 200
```
For more information, see [SELECT - ORDER BY Clause (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15) and [TOP (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*.
## MySQL Usage
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON
FOR EACH ROW
[{ FOLLOWS | PRECEDES } ]
```
### Examples
;` | `DROP TRIGGER ;` | Compatible syntax. |
| Modify trigger code | Use the `ALTER TRIGGER` statement. | Not supported | |
| Turn on and turn off a trigger | Use the `ALTER TRIGGER ENABLE;` and `ALTER TRIGGER DISABLE;` | Not supported | A common workaround is to use a database table with flags indicating which trigger to run. Modify the trigger code using conditional flow control (IF) to query the table and determine whether or not the trigger should run additional code or exit without performing any modifications to the database. |
| Triggers on views | `INSTEAD OF` triggers only | Not supported | |
For more information, see [Trigger Syntax and Examples](https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html) and [CREATE TRIGGER Statement](https://dev.mysql.com/doc/refman/5.7/en/create-trigger.html) in the *MySQL documentation*.
# User-defined functions for T-SQL
([{ [AS] [= ] [READONLY]} [,...n]])
RETURNS
[AS]
BEGIN
RETURN
END[;]
```
**Examples**
Create a scalar function to change the first character of a string to upper case.
```
CREATE FUNCTION dbo.UpperCaseFirstChar (@String VARCHAR(20))
RETURNS VARCHAR(20)
AS
BEGIN
RETURN UPPER(LEFT(@String, 1)) + LOWER(SUBSTRING(@String, 2, 19))
END;
```
```
SELECT dbo.UpperCaseFirstChar ('mIxEdCasE');
Mixedcase
```
### User-Defined Table-Valued Functions
([{ [AS] [= ] [READONLY]} [,...n]])
RETURNS TABLE
[AS]
RETURN ()[;]
```
**Examples**
Create a table valued function to aggregate employee orders.
```
CREATE TABLE Orders
(
OrderID INT NOT NULL PRIMARY KEY,
EmployeeID INT NOT NULL,
OrderDate DATETIME NOT NULL
);
```
```
INSERT INTO Orders (OrderID, EmployeeID, OrderDate)
VALUES
(1, 1, '20180101 13:00:05'),
(2, 1, '20180201 11:33:12'),
(3, 2, '20180112 10:22:35');
```
```
CREATE FUNCTION dbo.EmployeeMonthlyOrders
(@EmployeeID INT)
RETURNS TABLE AS
RETURN
(
SELECT EmployeeID,
YEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth,
COUNT(*) AS NumOrders
FROM Orders AS O
WHERE EmployeeID = @EmployeeID
GROUP BY EmployeeID,
YEAR(OrderDate),
MONTH(OrderDate)
);
```
```
SELECT *
FROM dbo.EmployeeMonthlyOrders (1)
EmployeeID OrderYear OrderMonth NumOrders
1 2018 1 1
1 2018 2 1
```
### Multi-Statement User-Defined Table-Valued Functions
([{ [AS] [= ] [READONLY]} [,...n]])
RETURNS <@Return Variable> TABLE
[AS]
BEGIN
RETURN
END[;]
```
For more information, see [CREATE FUNCTION (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*.
## MySQL Usage
([[,...]])
RETURNS [characteristic ...]
```
```
characteristic:
COMMENT '' | LANGUAGE SQL | [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
```
### Migration Considerations
{
FROM )}
```
### Examples
, )]
```
View the original seed value of an `IDENTITY` column with the `IDENT_SEED` system function.
```
SELECT IDENT_SEED ()
```
Reseed an `IDENTITY` column.
```
DBCC CHECKIDENT (, RESEED, )
```
#### Examples
[AS ]
START WITH
INCREMENT BY ;
```
```
ALTER SEQUENCE
RESTART [WITH ]
INCREMENT BY ;
```
#### Examples
[, , ]) [AS ]
```
```
NEWSEQUENTIALID()
```
#### Examples
AUTO_INCREMENT = `.
### Syntax
( [NOT NULL | NULL]
AUTO_INCREMENT [UNIQUE [KEY]] [[PRIMARY] KEY]...
```
### Migration Considerations
) FROM FOR UPDATE;
```
**Note**
The `FOR UPDATE CLAUSE` is required to maintain locks on the column until the read completes.
Aurora MySQL then increments the value retrieved by the preceding statement and assigns it to the in-memory autoincrement counter for the table. By default, the value is incremented by one. You can change the default using the `auto_increment_increment` configuration setting. If the table has no values, Aurora MySQL uses the value 1. You can change the default using the `auto_increment_offset` configuration setting.
Every server restart effectively cancels any `AUTO_INCREMENT = ` table option in `CREATE TABLE` and `ALTER TABLE` statements.
Unlike `IDENTITY` columns in SQL Server, which by default don’t allow inserting explicit values, Aurora MySQL allows explicit values to be set. If a row has an explicitly specified `AUTO_INCREMENT` column value and the value is greater than the current counter value, the counter is set to the specified column value.
### Examples
AUTO_INCREMENT = ` | |
| Column restrictions | Numeric | Numeric, indexed, and no `DEFAULT` | |
| Controlling seed and interval values | `CREATE/ALTER TABLE` | `auto_increment_increment` `auto_increment_offset` | Aurora MySQL settings are global and can’t be customized for each column as in SQL Server. |
| Sequence setting initialization | Maintained through service restarts | Re-initialized every service restart | For more information, see [Sequence Value Initialization](#chap-sql-server-aurora-mysql.tsql.identitysequences.mysql.initialization). |
| Explicit values to column | Not allowed by default, `SET IDENTITY_INSERT ON` required | Supported | Aurora MySQL requires explicit NULL or 0 to trigger sequence value assignment. Inserting an explicit value larger than all others will reinitialize the sequence. |
| Non PK auto enumerator column | Supported | Not Supported | Implement an application enumerator. |
| Compound PK with auto enumerator column | Supported | Not Supported | Implement an application enumerator. |
For more information, see [Using AUTO\$1INCREMENT](https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html), [CREATE TABLE Statement](https://dev.mysql.com/doc/refman/5.7/en/create-table.html), and [AUTO\$1INCREMENT Handling in InnoDB](https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization.html) in the *MySQL documentation*.
# Managing statistics for T-SQL
ON ( [,...])
[WHERE ]
[WITH ;
```
### Examples
[,...];
```
```
CREATE TABLE ( ) | ALTER TABLE
STATS_PERSISTENT = <1|0>,
STATS_AUTO_RECALC = <1|0>,
STATS_SAMPLE_PAGES = ;
```
### Migration Considerations