Best Practices for Creating Stored Procedures In SQL

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.

Benefits of Using Stored Procedures

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.

Creating a Stored Procedure

In this section, you’ll learn how to create a basic stored procedure in SQL and discover some best practices for using it.

Getting Started

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:

Creating a Basic Stored Procedure in SQL

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:

CREATE PROCEDURE procedure_name 
AS 
BEGIN 
sql_statement 
END  

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 
ASBEGIN 
SELECT JobTitle 
  FROM [AdventureWorks2017].[HumanResources].[Employee] 
  WHERE Gender = 'F' 
END; 

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:

EXEC GetTitleForFemaleHREmployees;

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:

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, 
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 ( 
	@Gender NCHAR(1), 
	@EmployeeCount INT OUTPUT 
) 
AS 
BEGIN 
SELECT JobTitle 
  FROM [AdventureWorks2017].[HumanResources].[Employee] 
  WHERE Gender = @Gender 

SELECT @EmployeeCount = @@ROWCOUNT; 
END; 

To call this procedure, declare the variable @Count to receive the output value as follows

DECLARE @Count INT; 

EXEC GetTitleForHREmployees 
	@Gender = 'F', 
	@EmployeeCount = @Count OUTPUT; 

SELECT @Count AS 'Employee Count'; 
 

The results are:

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 ( 
	@Gender NCHAR(1), 
	@NumberOfYearsHired INT, 
	@EmployeeCount INT OUTPUT, 
	@MaxVacation INT OUTPUT 
) 
AS 
BEGIN 
SELECT JobTitle 
  FROM [AdventureWorks2017].[HumanResources].[Employee] 
  WHERE Gender = @Gender and DATEDIFF(YEAR, HireDate, GETDATE()) < @NumberOfYearsHired; 

SELECT @EmployeeCount = @@ROWCOUNT; 

SELECT @MaxVacation = MAX(VacationHours) 
  FROM [AdventureWorks2017].[HumanResources].[Employee] 
  WHERE Gender = @Gender and DATEDIFF(YEAR, HireDate, GETDATE()) <= @NumberOfYearsHired; 
END; 

Execute the following to call the procedure:

EXEC GetTitleForHREmployeesWithMaxVacation 
	@Gender = 'F', 
	@NumberOfYearsHired = 10, 
	@EmployeeCount = @Count OUTPUT, 
	@MaxVacation = @MaxVac OUTPUT; 

SELECT @Count AS 'Employee Count'; 
SELECT @MaxVac AS 'Maximum Vacation'; 
 

These are the results:

Best Practices for Creating Stored Procedures

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.

Use SET NOCOUNT ON

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.

Keep Things Organized

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.

Create Temporary Tables at the Beginning

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.

Avoid Using Functions in Joins

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.

Be Cautious with Subqueries

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.

Use Consistent Naming

Always maintain a consistent naming scheme for all objects, columns, and variable names.

Specify Schema Prefixes

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:

Conclusion

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.