Why we use Stored Procedure instead of Query?

In our previous two posts, we explained the concept of stored procedures and demonstrated how to create them. If you haven’t read these previous posts, I strongly recommend doing so before proceeding with this one.

 How to Create stored procedure with input parameters

• Create Stored Procedure with return values

In the article, we will discuss the below points

• Advantages and disadvantages of stored procedure
• Difference between inline Sql query and stored procedure
• Drawbacks, the reasons for not to using stored procedures

Disadvantages of stored procedures in Microsoft SQL?

Advantages of using SQL stored procedures

• 
  
• Stored procedures offer enhanced performance and can reduce network traffic load. Let's explore the advantages of using stored procedures individually.
• 
  
• Stored procedures have the advantage of reusing execution plans. But what exactly does this mean? When you execute a query in SQL Server, it typically goes through three steps:
• 
  
• 1. Verifying the syntax of the query.
• 2. Compiling the SQL query.
• 3. Creating an execution plan."

So when we reissue the same query, the SQL Server can utilize the cached execution plan, thus saving time and resources.

Stored procedures excel at retaining and caching execution plans for reuse.

In modern versions of SQL Server, SQL queries can also benefit from execution plan reuse. However, even slight alterations to the SQL query can prevent the reuse of the execution plan.

Let's delve into an example:

We have a table named TblOrganizationEmployee, comprising the following columns:

• Id
• EmployeeName
• EmployeeSalary
• Country
• Address
• DepartmentId

This table contains information about employees within an organization, including their name, salary, country, address, and department ID.

Table SQL script

/****** Object: Table [dbo].[TblOrganizationEmployee] Script Date: 10/06/2020 7:12:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblOrganizationEmployee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeName] [nvarchar](max) NULL,
[EmployeeSalary] [float] NULL,
[Country] [nvarchar](max) NULL,
[Adress] [nvarchar](max) NULL,
[DepartmentId] [int] NULL,
CONSTRAINT [PK_TblEmployeeSalary] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
GO
SET IDENTITY_INSERT [dbo].[TblOrganizationEmployee] ON
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (1, 'Hunter', 80000, 'UK', 'Osaka', 1)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (2, 'Connor', 70000, 'India', 'Kyoto', 1)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (3, 'Ezra', 60000, 'USA', 'Vienna, Austria', 2)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (4, 'Aaron', 55000, 'USA', 'Tokyo', 3)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (5, 'Adrian', 85000, 'India', 'Amsterdam, Netherlands', 3)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (6, 'Easton', 72000, 'India', 'Barcelona, Spain', 1)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (7, 'Colton', 85000, 'Australia', 'Singapore', 1)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (8, 'Angel', 42000, 'Australia', 'Monte Carlo, Monaco', 3)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (9, 'Jaxson', 32000, 'USA', 'Cologne, Germany', 2)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (10, 'Greyson', 45000, 'Australia', 'Amsterdam, Netherlands', 1)
GO
INSERT [dbo].[TblOrganizationEmployee] ([Id], [EmployeeName], [EmployeeSalary], [Country], [Adress], [DepartmentId]) VALUES (11, 'Brayden', 78000, 'UK', 'Tokyo', 1)
GO
SET IDENTITY_INSERT [dbo].[TblOrganizationEmployee] OFF
GO

Now let’s say we want all employees from the table whose Salary is greater than 50000.

select * from TblOrganizationEmployee where EmployeeSalary>50000

When you execute the above SQL query, here's what happens behind the scenes:

The first time the query is executed, the SQL server checks the syntax, compiles the query, and generates an execution plan. Then, the database engine executes the query and returns the data.

Upon subsequent executions of the same query, the SQL server reuses the cached execution plan, resulting in faster data retrieval.

However, if you modify the WHERE clause parameter, such as filtering by EmployeeSalary equals 40000, the SQL server cannot reuse the existing execution plan. Instead, it creates a new execution plan specifically for this altered query.

select Name from [dbo].[Users] where Id>40000

But in the case of the stored procedure, it reuses the execution plan. Take a look at the stored procedure below:

Here's a simple stored procedure that takes `@EmployeeSalary` as a parameter.

Create Procedure [dbo].[spGetOrganizationEmployee]
@EmployeeSalary int
as
Begin
select * from TblOrganizationEmployee where EmployeeSalary>@EmployeeSalary
End

So, to execute the stored procedure and retrieve all employees with a salary greater than the value passed in, we use the following syntax:

spGetOrganizationEmployee 4000

When executing the above query, the same process occurs: the procedure is compiled, and an execution plan is created. Upon subsequent executions, the execution plan is reused.

Even if we change the @EmployeeSalary parameter to 50000, the same plan will be reused in the case of stored procedures, unlike with SQL queries.

• Reduce the Network traffic

Another great advantage of the stored procedure is that they can reduce Network traffic load.
Let’s understand that with an example.

Create Procedure [dbo].[spGetOrganizationEmployee]
@EmployeeSalary int
as
Begin
select * from TblOrganizationEmployee where EmployeeSalary>@EmployeeSalary
End

If you examine the stored procedure below, it's a simple one-liner.

Typically, the body of a stored procedure, enclosed within the Begin and End blocks, contains all the logic. However, in reality, stored procedures can span thousands of lines of code, containing extensive logic within their bodies.

When dealing with such large stored procedures, executing them is straightforward from the client-side application, whether it's a PHP, .NET, or Java application. All that's required is to specify the procedure name and the parameter values, and then the procedure can be executed.

spGetOrganizationEmployee 4000

So over the network, only two words get transmitted just

spGetOrganizationEmployee 4000

If we lack a stored procedure for the task, we resort to writing an SQL query and sending it from the client tools.

In such a scenario, we're compelled to transmit all those 5000 lines of code, which inevitably impacts network traffic.

• Reusability and better maintainability

As we know, stored procedures reside on the server, and multiple applications can reuse them.

For instance, if several applications accessing our database require similar data, we can create a stored procedure that they can easily call.

The advantage of creating a stored procedure is that you can modify the logic within the procedure without requiring updates to App1, App2, and App3.

This means you only need to change the code in one place, regardless of how many applications use it. By reusing the same piece of code across multiple applications, it results in higher performance.

• Security

Stored procedures are developed with authorization in mind, allowing you to set permissions for who can access them.

You can grant permissions to specific users while restricting access for others.

Let's consider an example of this scenario:

Suppose we have the following stored procedure in our database, which is utilized by multiple applications.

Create Procedure [dbo].[spGetOrganizationEmployee]
@EmployeeSalary int
as
Begin
select * from TblOrganizationEmployee where EmployeeSalary>@EmployeeSalary
End

As salary information is confidential within any organization, I want to allow all users to access the above stored procedure.

However, I also want to deny access to specific users so that they cannot utilize the stored procedure.