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.

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.

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.