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 (
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.
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
SQL Server allows you to shorten the expression to:
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 (
@account) which it uses respectively to filter the
customer and update the
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.
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
1, we will see that its value has been correctly updated:
SELECT bankAccount FROM Customers WHERE customerId = 1;
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:
We declare two new internal variables:
@newBankAccountthat will save the value of the
bankAccountbefore and after performing the
@bankAccountResultwith a message showing the values of
We return the value of
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:
|The previous account: 111111111 has been modified to: 222222222|
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.