Baseline your servers and optimize your applications with Site24x7 SQL monitoring tool.
Structured Query Language (SQL) is one of the most prominent languages for database management, offering extensive flexibility to the knowledgeable developer. One component that enhances the efficiency, reusability, and security of database management is the stored procedure.
A stored procedure is a group of SQL statements that are created and stored in a database management system, allowing multiple users and programs to share and reuse the procedure. A stored procedure can accept input parameters, perform the defined operations, and return multiple output values. This enables users to provide different inputs to manipulate or retrieve specific portions of the data. Then, when one user modifies the stored procedure, all users will receive that update. Most relational databases like MySQL and SQL Server support stored procedures.
This hands-on article reviews how to create a stored procedure in SQL and highlights best practices.
Consider a situation where you have one or more queries that must run multiple times at regular intervals. These queries must run alongside complex logic in your application to apply business logic to the data. This process requires fetching the data from the database, applying the business logic to modify the data, and updating the database.
Fortunately, stored procedures let you extend the functionality of the database by writing code blocks to process data. Then, you can call the database to execute the stored procedure, which applies the business logic. The result is reduced network traffic and considerably improved application performance.
Another benefit of stored procedures is their reusability. A stored procedure is kept in the database, compiled once, and used multiple times, whereas an SQL query to the database must be compiled every time. Stored procedures also enhance database security, allowing you to restrict security by granting a user access to a specified procedure instead of the whole database. Additionally, using stored procedures hides the underlying details of a database (such as table names), as the user accesses it using the stored procedure’s name.
In this section, you’ll learn how to create a basic stored procedure in SQL and discover some best practices for using it.
For this tutorial, you’ll use the AdventureWorks database. AdventureWorks is a sample database for Microsoft SQL Server that supports standard online transaction processing (OLTP) scenarios for a fictitious manufacturing company called Adventure Works Cycles. To follow along, download and install SQL Server 2019 and SQL Server Management Studio 18. Then, download the OLTP backup file called AdventureWorks2019.bak, and follow the instructions to restore the sample database to your SQL server instance.
You should now have a database like this:Fig. 1: AdventureWorks database.
To begin, create a new query in SQL Server by clicking New Query, or open a new query tab by pressing Ctrl + N.
The syntax for creating a stored procedure is as follows:
Accordingly, to create a procedure that retrieves the titles of all the female human resources employees from the HumanResources.Employee table, enter the following script:
CREATE PROCEDURE GetTitleForFemaleHREmployees
WHERE Gender = 'F'
Then, press F5 to execute the command, which compiles and saves the stored procedure.
Finally, call your newly created procedure by running the execute (EXEC) command:
To run this command on the same query tab, highlight it and click Run. Otherwise, open a new query tab to run it from there. Either method should return the data pictured below:Fig.2: Results of a stored procedure to obtain the job titles of all female HR employees.
In the example above, gender is hard-coded into the procedure. However, you can make it more flexible by allowing the user to specify the gender using a parameter. You can also use parameters to return custom data to the calling application.
Parameters are used to exchange data between a procedure and an application. There are two types of parameters: input and output. Input parameters accept data from the calling application, and output parameters return data to the calling application.
Parameters are added as comma-separated arguments after the procedure name. To create an input and an output parameter, use the following syntax, respectively:
parameter_name data_type OUTPUT
Including a SELECT statement in the body of a stored procedure will return the results to the calling application. However, you can use output parameters to return custom data.
Now it’s time to improve on the previous example. You’ll create a procedure to retrieve the job titles of all human resources employees based on the gender specified in the input parameter and return the number of results. Use the system variable @@ROWCOUNT to get the number of rows read by the previous statement:
CREATE PROCEDURE GetTitleForHREmployees (
@EmployeeCount INT OUTPUT
WHERE Gender = @Gender
SELECT @EmployeeCount = @@ROWCOUNT;
To call this procedure, declare the variable @Count to receive the output value as follows
DECLARE @Count INT;
@Gender = 'F',
@EmployeeCount = @Count OUTPUT;
SELECT @Count AS 'Employee Count';
The results are:Fig. 3: Results of a stored procedure with input and output parameters.
It’s also easy to filter your query further, such as if you want to retrieve the job titles of all the employees of a certain gender that have been hired for a certain number of years or less. You can include multiple parameters by adding to the comma-separated list of arguments. This allows you to create a procedure to filter by the number of years hired and return the maximum vacation hours that a single employee from that list has taken:
CREATE PROCEDURE GetTitleForHREmployeesWithMaxVacation (
@EmployeeCount INT OUTPUT,
@MaxVacation INT OUTPUT
WHERE Gender = @Gender and DATEDIFF(YEAR, HireDate, GETDATE()) < @NumberOfYearsHired;
SELECT @EmployeeCount = @@ROWCOUNT;
SELECT @MaxVacation = MAX(VacationHours)
WHERE Gender = @Gender and DATEDIFF(YEAR, HireDate, GETDATE()) <= @NumberOfYearsHired;
Execute the following to call the procedure:
@Gender = 'F',
@NumberOfYearsHired = 10,
@EmployeeCount = @Count OUTPUT,
@MaxVacation = @MaxVac OUTPUT;
SELECT @Count AS 'Employee Count';
SELECT @MaxVac AS 'Maximum Vacation';
These are the results:Fig. 4: Results of a stored procedure with multiple input and output parameters.
In the previous section, you learned how to create basic stored procedures. However, the use cases for stored procedures can require complex logic. This section discusses some best practices to follow when creating stored procedures.
When each statement is executed in a stored procedure, the SQL Server returns the number of rows that are affected as part of the results. To reduce network traffic and improve performance, use SET NOCOUNT ON at the beginning of the stored procedure. The image below shows the results of toggling NOCOUNT.Fig. 5: Results of SET NOCOUNT ON.
The queries should be broken down into logical groups and encapsulated in the BEGIN/END blocks. In addition, the code should be appropriately indented with sufficient comments.
All Data Definition Language (DDL) statements should appear at the beginning. DDL statements, like CREATE, ALTER, TRUNCATE, and DROP, are used to create a database schema and define the type and structure of data that will be saved in the database. Temporary tables should be created at the top of the procedure using DDL statements.
Don’t use functions in joins because they will cause performance issues. The function will be called for every record in the result set, and indexes for columns you’re filtering on that are surrounded by a function will not be used. Instead, save the output of a function in a temporary table and use the temporary table in the join.
When writing SQL stored procedures, subqueries offer significant utility, but can often challenge performance and code interpretability, particularly when working with extensive datasets or complex logic. Preferred alternatives include SQL joins or temporary tables when suitable. Keep in mind, though, many of today's database systems are capable of optimizing subqueries effectively, meaning they are not always an issue. When crafting your stored procedures, the goal should be a balance between efficiency, ease of maintenance, and flexibility.
Always maintain a consistent naming scheme for all objects, columns, and variable names.
Avoid making assumptions about which schema a table belongs to. Think of the schema as a namespace. If you have objects with similar names in different schemas, they can be easily resolved without any issues if you specify the schema. You should specify the schema even if you have a single one because if you add more in the future, you’ll have to update your code. Here’s how we specified the schema in one of the examples above:Fig. 6: An example of specifying a schema prefix.
This article provided a brief overview of what stored procedures are, demonstrated how to create a basic stored procedure, and outlined the benefits and best practices for creating stored procedures.
A stored procedure is one of many ways to access data in a database, and it offers many benefits. Using stored procedures can greatly improve the performance of your application by reducing network traffic, eliminating unnecessary logic in your code, and isolating and storing pertinent logic in the database.
Learn how to optimize and improve SQL queries on SQL servers handling large datasets following the best practices. Learn More➤
Amazon S3 security best practices help you analyze the security of your S3 buckets and operate your AWS ecosystem optimally. Learn more!➤
Write for Site24x7 is a special writing program that supports writers who create content for Site24x7 “Learn” portal. Get paid for your writing.Apply Now