SQL Server Stored Procedures

In this post we will learn what is stored procedure, how it is different from query and what are its advantages over queries.

Procedures

In programming, a procedure refers to a set of instructions or a routine that performs a specific task or a series of tasks. It is a block of code that can be called and executed as a unit. Procedures are a fundamental concept in procedural programming. There are two main types of procedures: functions and subroutines.

Functions are procedures that return a value. On the other hand, procedures, also known as subroutines or methods, are blocks of code that perform a specific task but do not return a value.

Stored Procedures

A stored procedure is a database object which is stored at database server and can be executed later when required. A stored procedure is a precompiled SQL query stored at database server which can be executed later. Therefore, we find three important points.

1. Pre-compiled query
2. Stored at database
3. Executed later when required

Definition

A stored procedure in SQL Server is a precompiled and stored collection of one or more Transact-SQL statements, which can be invoked with a single name or a set of parameters

Since stored procedure is a compiled query, we see how a query is compiled.

Query Compilation Steps

1. The syntax of query is checked
2. Different execution plans are created
3. Optimal execution plan is selected and saved/cached

Stored Procedure Compilation

When stored procedure is compiled, an execution plan is created for the stored query so that the query will be executed in optimal way. When stored procedure is executed then this execution plan is used.

Drawbacks of query at client side

Before looking at the advantages of stored procedures, we look at the disadvantages of query compared to stored procedures.

Network traffic: Each client will send the same code of query to the SQL Server which will create network traffic. More the clients more will be the network traffic which will affect the network bandwidth and may create traffic jam. It will affect the performance of the application.

Maintenance of code: The query code is difficult to maintain because if code is changed then it must be updated at all clients.

Code tampering: The code may be tampered at a client side because security maintenance of the code is difficult if the number of clients increases. Security checks must be maintained at all client sites.

Advantage of Stored Procedure over Query

Code maintenance: It is easy to maintain stored procedure because it is stored at a centralized location.

Security: Stored procedure can be encrypted and hence it provides security of code. Secondly, only those who are authorized to execute the stored procedures can execute them without giving them direct access to the underlying tables. Third, security checks are needed at a centralized server location.

Performance: Each time a query is executed then its syntax is checked and optimal execution plan is created. But in case of stored procedure, pre- compiled query is executed and so this process of checking syntax and selecting optimal execution plan is not repeated again and again. Execution plan can be reused, staying cached in SQL Server's memory, reducing server overhead. This improves the performance of stored procedure. Secondly, stored procedure is saved at database server. So, there is no overhead of network traffic as is in case of query. A query is first sent to server and then it is executed but in case of stored procedure, it is already at the server.

Modular: Stored procedures encapsulate a series of SQL statements into a single unit. This helps in organizing and modularizing code, making it easier to maintain and understand.

Reusability: Stored procedures are similar to procedures or functions in procedural programming languages and can be reused.

Operations: The stored procedure, view or function can be stored at server but they have some differences. The stored procedure allows input parameters, output parameters, DDL and DML operations and code execution plans. The views and functions lack one or more of these features.

Look at the following table. We find that views allows DML operations but lacks parameters, DDL and code execution plan. Functions allow parameters but does not allow DDL, DML and code execution plan. It allows Select DML only. But stored procedures provide all such features. The stored procedure has unique feature of code execution plan which makes its execution performance better than views and functions.

	Input	Output	DDL	DML	Code Exec.
Functions	Yes	Yes	No	No, Select only	No
Views	No	No	No	Yes	No
Stored Procedures	Yes	Yes	Yes	Yes	Yes

In the next post, we will learn how to create stored procedure.