Introduction of Stored Procedures

Table of Contents

• Introduction
• Advantage of Stored Procedure
• Types of Stored Procedure
• Difference between Stored Procedure and Function

Introduction

We are developing an application. We have a requirement to create web pages for students, teachers and admin registration. So we can create various web pages and write code for each page for registration, in other words we need to write an insert statement for each type profile registration on their individual pages.



To avoid writing an insert statement on each page we can write code one time and save in the database and can reuse that code on each page. For this purpose we use a Stored Procedure that has reusable code saved in the database that can be called as required. We use a single peice of code on each page using a Stored Procedure.



A Stored Procedure can contain SELECT, INSERT, UPDATE and DELETE statements, in other words we can have a set of Data Manipulation Language (DML) and Data Definition Language (DDL) statements in a single Stored Procedure. It is used whenever we need a single statement or set of statements multiple times in the application.



Advantage of Stored Procedure

Stored Procedure have many advantages over a query. These are:

1. Security
   
   Stored Procedures can also act as an additional security layer. We pass data as a parameter in a Stored Procedure so SPs avoid SQL injection. We can also implement a security model on SPs rather than tables or views.
    
2. Performance
   
   SPs improve performance, since these are pre-compiled and their execution plan is cached and used again when the same SP is executed again.
    
3. Network Traffic
   
   SPs are stored in the database so the amount of data passes over a network is reduced.
    
4. Transaction
   
   The statements in the SP are within a transaction scope, which means that if SPs have multiple DML statements and one statement execution fails then all data will be rolled back. Either all of the SQL statements in a Stored Procedure will execute, or none will. This is known as atomicity.
    
5. Reuse and Maintains
   
   An SP is a set of statements. That SP can be used multiple times in an application so there is no need to write that code multiple times. Suppose we want to add a new field, then we only need to update a single location, that is the SP, instead individual code files so it reduces development cost and modification is much easier.

Types of Stored Procedure

There are four types of Stored Procedures in SQL Server. These are:

• System Defined Stored Procedure
• Extended Stored Procedure
• User-Defined Stored Procedure
• CLR Stored Procedure

Now let's explain one by one in brief.

1. System Defined Stored Procedure
   
   SQL Server has a resource database that is a read-only system database that contains all system object (SPs,Tables etc.). You will not see the resource database in the SQL Server Management Studio (SSMS) Object Explorer window, but the system objects persisted in the resource database will logically appear in every database on the server. These Stored Procedure start with the "sp_" prefix so when we use the "sp_" prefix with a User-Defined Stored Procedure then that Stored Procedure definition is first looked for in the system database (resource database) and then looked for in the User-Defined database so don't use the "sp_" prefix with a User-Defined Stored Procedure to improve performance.
    
2. Extended Stored Procedure
   
   SQL Server has a master database where extended Stored Procedures are stored. An Extended Stored Procedure is a C or C++ DLL that can be called from Transact-SQL using the same syntax as calling a Transact-SQL Stored Procedure. These extended procedures start with the xp_ prefix. However, Extended Stored Procedures are deprecated and you should avoid using them if possible. CLR Integration should be used instead.
    
3. User-Defined Stored Procedure
   
   These procedures are created by the user for their own actions. Stored Procedures contain logic to perform that action. These can be created in all system databases except the Resource database or in a User-Defined database. We should avoid the "sp_" prefix with the name of the User-Defined Stored Procedure.
    
4. CLR Stored Procedure
   
   It is a managed object that runs under database memory so it ensures type safety and memory management. Common Language Runtime (CLR) Stored Procedures take benefit of .NET classes and thus make it easy to implement complex logic, calculation, intense string operations, complex iterations, data encryption, etc. that are difficult to obtain in standard Stored Procedures.

Difference between Stored Procedure and Function

Sr. No.	Stored Procedure	Function
1.	It can return zero or multiple values. To get values returned we use an OUTPUT parameter.	It always returns a value that can be scalar or table type.
2.	We can pass two types of parameters in SPs, one is an input parameter and the other is output parameter.	It takes only input parameters.
3.	We can maintain a transaction in the SP.	We can't maintain a transaction in the Function
4.	We can only call a SP in an execution statement i.e EXEC statement, which means we can't call a SP in a SELECT statement	We can call a function in a SELECT/WHERE/HAVING section
5.	We can perform exception handling in SP using try catch block.	We can't perform exception handling in the Function.
6.	We can call a Function in the SP.	We can't call a SP in the Function.
7.	SPs allow Data Manipulation Language (DML) statements, in other words we can use INSERT, UPDATE and DELETE statements in SP.	A Function allows only a SELECT statement.