Stored procedures in Transact SQL

Stored procedures are sets of one or more statements stored in the database, which can be called from another part of the application. stored procedures allow you to receive input and/or output parameters, although it is not always necessary for them to return information. Furthermore, and unlike functions, they can perform internal operations that alter the database such as INSERTS, UPDATES and/or DELETES.

Features and types

• They can perform internal operations that alter the database state (INSERT INTO…)

• They can call other stored procedures.

• By encapsulating blocks of code, they improve network traffic between client and server. Additionally, it improves efficiency by encouraging code reuse.

• They provide greater security. For example, stored procedures can be encrypted, and the use of parameters protects against direct injection of SQL code.

• Improve performance and make the maintenance easier.

• Improve performance and make maintenance easier.

Based on the types of procedures, we can divide then into:

• User-defined procedures: those that the user defines freely and personalizes according to his needs and business logic.

• Temporary procedures: those defined by the user,they are like a permanent procedure but stored in tempbd. They are deleted after the session in which they are used.

• System procedures: those that are included with SQL Server.

• Extended user-defined procedures: enable creating external routines in a programming language such as C. These types of procedures will be removed in future versions of SQL Server, so we discourage their use.

PROCEDURES

CREATE PROCEDURE

CREATE PROCEDURE is the statement to create new user-built stored procedures. The basic syntax is the following:

CREATE PROCEDURE (parameters IN/OUT)
AS
BEGIN
    [code]
END

That said, we are going to use a new procedure that allows us to add and/or modify the bankAccount of the users stored in the Customers table as an example:

CREATE PROCEDURE insertModifyBankAccount
(
    @id    INT,
    @bankAccount INT
)
AS
BEGIN
    UPDATE Customers
    SET bankaccount = @bankaccount
    WHERE customerId = @id
END

The new procedure: insertModifyAccount receives two input parameters (@id, @account) which it uses respectively to filter the customer and update the account value.

The following example is intended to illustrate in the simplest and most schematic way how a stored procedure works. However, it should be noted that the use of control structures and error handling is highly recommended when working with procedures. In the same way and if we have a sequence of instructions, it can be useful to handle transactions so as not to cause changes in the database if an error occurs during execution. In the following sections: ERROR HANDLING and TRANSACTIONS you will find more information about it.

EXECUTE

The EXEC statement allows executing procedures. There are two quick ways to execute a stored procedure:

EXEC insertModifyBankAccount @id  = 1, @bankAccount = 111111111

or we can declare previously:

DECLARE @RC int
DECLARE @id int
DECLARE @bankAccount int

EXECUTE @RC = [dbo].[insertModifyBankAccount]
   1
  ,111111111
GO

In both cases, if we consult the customer’s bankAccount with customerId = 1, we will see that its value has been correctly updated:

SELECT bankAccount
FROM Customers
WHERE customerId = 1;

ALTER

ALTER PROCEDURE is the statement that allows you to modify stored procedures already declared by the user. The modifications can be diverse: name of the procedure, number and types of arguments, internal logic…

In the following example we are going to modify our procedure: insertModifyBankAccount so that it returns, in an output parameter, a message indicating the values of the bankAccount before and after it was modified.

ALTER PROCEDURE insertModifyBankAccount
(
    @id    INT,
    @bankAccount INT,
    @bankAccountResult VARCHAR(255) OUTPUT
)
AS
BEGIN
    DECLARE @prevBankAccount INT;
    DECLARE @newBankAccount INT;
    SET @prevBankAccount = (SELECT bankAccount
                            FROM Customers
                            WHERE customerId = @id);
    UPDATE Customers
    SET bankAccount = @bankAccount
    WHERE customerId = @id
    SET @newBankAccount = (SELECT bankAccount
                          FROM Customers
                          WHERE customerId = @id);
    SET @bankAccountResult = 'The previous bank account: ' +
                            CAST (@prevBankAccount AS VARCHAR(20)) +
                            ' has been modified to: '+
                            CAST (@newBankAccount AS VARCHAR (20));
   SELECT @bankAccountResult AS bankAccountResult
END
GO

The changes made are as follows:

• We add a new output parameter: @bankAccountResult

• We declare two new internal variables: @prevBankAccount and @newBankAccount that will save the value of the bankAccount before and after performing the UPDATE.

• We fill @bankAccountResult with a message showing the values of @prevBankAccount and @newBankAccount.

• We return the value of @bankAccountResult in a SELECT.

Let’s proceed to run the procedure again, taking into account the new output parameter:

EXEC insertModifyBankAccount @id  = 1, @bankAccount = 222222222, @bankAccountResult = null;

We see that it returns as output:

DROP PROCEDURE

The DROP PROCEDURE statement allows you to completely delete stored procedures generated within SQL Server.

DROP PROCEDURE IF EXISTS insertModifyAccount;

As in other types of DROP, it is recommended to always use the IF EXISTS clause.