Stored procedures for T-SQL

This topic provides reference content comparing stored procedures in Microsoft SQL Server 2019 and Amazon Aurora MySQL. You can understand the key differences and similarities between these two database systems' implementations of stored procedures.

SQL Server Usage

Stored procedures are encapsulated, persisted code modules you can run using the EXECUTE T-SQL statement. They may have multiple input and output parameters. Table-valued user-defined types can be used as input parameters. IN is the default direction for parameters, but OUT must be explicitly specified. You can specify parameters as both IN and OUT.

In SQL Server, you can run stored procedures in any security context using the EXECUTE AS option. They can be explicitly recompiled for every run using the RECOMPILE option and can be encrypted in the database using the ENCRYPTION option to prevent unauthorized access to the source code.

SQL Server provides a unique feature that allows you to use a stored procedure as an input to an INSERT statement. When you use this feature, only the first row in the data set returned by the stored procedure is evaluated.

As part of the stored procedure syntax, SQL Server supports a default output integer parameter that can be specified along with the RETURN command, for example, RETURN -1. It’s typically used to signal status or error to the calling scope, which can use the syntax EXEC @Parameter = <Stored Procedure Name> to retrieve the RETURN value, without explicitly stating it as part of the parameter list.

Syntax

CREATE [ OR ALTER ] { PROC | PROCEDURE } <Procedure Name>
[<Parameter List>
[ WITH [ ENCRYPTION ]|[ RECOMPILE ]|[ EXECUTE AS ...]]
AS {
[ BEGIN ]
<SQL Code Body>
[RETURN [<Integer Value>]]
[ END ] }[;]

Creating and Running a Stored Procedure

Create a simple parameterized stored procedure to validate the basic format of an email.

CREATE PROCEDURE ValidateEmail
@Email VARCHAR(128), @IsValid BIT = 0 OUT
AS
BEGIN
IF @Email LIKE N'%@%'
    SET @IsValid = 1
ELSE
    SET @IsValid = 0
RETURN
END;

Run the procedure.

DECLARE @IsValid BIT
EXECUTE [ValidateEmail]
    @Email = 'X@y.com', @IsValid = @IsValid OUT;
SELECT @IsValid;

-- Returns 1

EXECUTE [ValidateEmail]
    @Email = 'Xy.com', @IsValid = @IsValid OUT;
SELECT @IsValid;

-- Returns 0

Create a stored procedure that uses RETURN to pass the application an error value.

CREATE PROCEDURE ProcessImportBatch
@BatchID INT
AS
BEGIN
    BEGIN TRY
        EXECUTE Step1 @BatchID
        EXECUTE Step2 @BatchID
        EXECUTE Step3 @BatchID
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 235
            RETURN -1 -- indicate special condition
        ELSE
            THROW -- handle error normally
    END CATCH
END

Using a Table-Valued Input Parameter

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),
(3, 'M6 Washer', 100);

Create a table-valued type for the OrderItem table-valued parameter.

CREATE TYPE OrderItems
AS TABLE
(
    OrderID INT NOT NULL,
    Item VARCHAR(20) NOT NULL,
    Quantity SMALLINT NOT NULL,
    PRIMARY KEY(OrderID, Item)
);

Create a procedure to process order items.

CREATE PROCEDURE InsertOrderItems
@OrderItems AS OrderItems READONLY
AS
BEGIN
    INSERT INTO OrderItems(OrderID, Item, Quantity)
    SELECT OrderID,
        Item,
        Quantity
    FROM @OrderItems
END;

Instantiate and populate the table valued variable and pass the data set to the stored procedure.

DECLARE @OrderItems AS OrderItems;

INSERT INTO @OrderItems ([OrderID], [Item], [Quantity])
VALUES
(1, 'M8 Bolt', 100),
(1, 'M8 Nut', 100),
(1, M8 Washer, 200);

EXECUTE [InsertOrderItems]
    @OrderItems = @OrderItems;

(3 rows affected)
   Item       Quantity
1  M8 Bolt    100
2  M8 Nut     100
3  M8 Washer  200

INSERT…​ EXEC Syntax

INSERT INTO <MyTable>
EXECUTE <MyStoredProcedure>;

For more information, see CREATE PROCEDURE (Transact-SQL) in the SQL Server documentation.

MySQL Usage

Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) stored procedures provide similar functionality to SQL Server stored procedures.

As with SQL Server, Aurora MySQL supports security run context. It also supports input, output, and bi-directional parameters.

Stored procedures are typically used for: * Code reuse — Stored procedures offer a convenient code encapsulation and reuse mechanism for multiple applications, potentially written in various languages, requiring the same database operations. * Security management — By allowing access to base tables only through stored procedures, administrators can manage auditing and access permissions. This approach minimizes dependencies between application code and database code. Administrators can use stored procedures to process business rules and to perform auditing and logging. * Performance improvements — Full SQL query text doesn’t need to be transferred from the client to the database.

Stored procedures, triggers, and user-defined functions in Aurora MySQL are collectively referred to as stored routines. When binary logging is enabled, MySQL SUPER privilege is required to run stored routines. However, you can run stored routines with binary logging enabled without SUPER privilege by setting thelog_bin_trust_function_creators parameter to true for the DB parameter group for your MySQL instance.

Aurora MySQL permits stored routines to contain control flow, DML, DDL, and transaction management statements including START TRANSACTION, COMMIT, and ROLLBACK.

Syntax

CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name
([ IN | OUT | INOUT ] <Parameter> <Parameter Data Type> ... )
COMMENT 'string' |
LANGUAGE SQL |
[NOT] DETERMINISTIC |
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } |
SQL SECURITY { DEFINER | INVOKER }
<Stored Procedure Code Body>

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.

CREATE PROC|PROCEDURE
<Procedure Name>
@Parameter1 <Type>,
...n
AS
<Body>

CREATE PROCEDURE
<Procedure Name>
(Parameter1
<Type>,...n)
<Body>

{ EXEC | EXECUTE } AS
{ CALLER | SELF | OWNER |
'user_name' }

DEFINER = 'user' |
CURRENT_USER

SQL SECURITY {
DEFINER | INVOKER }

For more information, see Stored Procedures and Functions and CREATE PROCEDURE and CREATE FUNCTION Statements in the MySQL documentation.