Benefits Behind Using the Stored Procedure

We can Define stored Procedure in SQL Server as the set of logically group of SQL statement they are grouped to perform a particular task. This article will describe the Stored Procedure in SQL Server and the benefits with stored procedures..

 Stored Procedure: We can define stored Procedure in SQLServer  as the set of logical group of SQL statements they are grouped to perform a particular task. There are so many benefits behind using a stored procedure. One of

 the main benefit behind using a stored procedure is that it increases the query performance of the database. The other benefits behind using the Stored Procedure are listed below.

Benefits Behind Using the Stored Procedure

1.     One of the main benefits behind using the stored procedure is that it reduces the amount of information sent to the database server. It can prove itself a more beneficial when the speed of the network is slow. Since if we send the SQL query (statement) which is getting executed repeatedly to the server through network and the same time network gets disconnected, then the execution of the SQL statement would not return the desired results, if the SQL query is not used between Transaction statement and rollback statement is not being used.

2.     Need to compile only once when the stored procedure is created. Then after it’s not required to recompile before executing until it is modified and reutilizes the same execution plan while the SQL statements need to be compiled every time whenever it is requested to be executed even if we send the same SQL statement all the time.

3.     It facilitates in re-usability of the SQL code as multiple users can reuse it and by multiple clients since we need to just execute the stored procedure at the place of writing the same SQL statement all the time. It also reduces the development time.

4.     Stored procedure is helpful in terms of enhancing the security as we can grant permission to the user for executing the stored procedure at the place of giving permission on the tables used that is being used in stored procedure.

5.     Sometimes, it is quiet useful to use the database for storing the business logic as a stored procedure since it makes it secured and in case any change is needed in the business logic, then we may need to make changes in the stored procedure only and not in the files contained on the web server.

Let’s see how to write a Stored Procedure in SQL Server

For Example I have created a table called students, below is the structure of the table:

CREATE TABLE  Students_Records

(

    [Student_id] [int] IDENTITY(1,1) NOT NULL,

    [First_name] [nvarchar](200) NOT  NULL,

    [Last_name] [nvarchar](200)  NULL,

    [Email_id] [nvarchar](100)  NULL

)

For Example we insert the following data into the above mentioned table:

Insert into students_Records (First_name, last_name, Email)

 Values('Ankit', 'Kumar', 'ankit@abc.com')

Insert into students_Records (First_name, last_name, Email)

 Values('Vikas', 'Singh', 'vikas@abc.com')

Insert into students_Records (First_name, last_name, Email)

 Values('Deepak', 'kumar', 'deepak@abc.com')

Insert into students_Records (First_name, last_name, Email)

 Values('Rahul', 'Johari', 'rahul@abc.com')

Insert into students_Records (First_name, last_name, Email)

 Values('Amit', 'Singh', 'amit@abc.com')

Now, at the time of writing a Stored Procedure, the first step would be to write the Create Procedure statement as the first statement:

Create Procedure Ankitproc

(

Input parameters ,

Output Parameters (If required)

)

As

Begin

     Sql statement used in the stored procedure

End

Now, suppose we have to create a Stored Procedure to return a student name

where student_id is given as an input parameter to the stored procedure. Then, the stored Procedure will be something like:

/*  Getstudentname is the name of the stored procedure created in this example below*/

Create  PROCEDURE Getstudentname(

Declare @studentid INT                   --Input parameter ,  Student_id of the student

)

AS

BEGIN

SELECT First_name+' '+Last_name FROM Students_records WHERE student_id=@student_id

END

We can collect the name of the student as well in the output parameter of the Stored Procedure. For example:

 /*

GetstudentnameInOutputVariable is the name of the stored procedure created in this example below which

uses output variable @Studentname to collect the student name returns by the

stored procedure

*/

Create  PROCEDURE GetstudentnameInOutputVariable

(

@studentid INT,                       --Input parameter ,  Studentid of the student

@studentname VARCHAR(200)  OUT        -- Out parameter declared with the help of OUT keyword

)

AS

BEGIN

SELECT @studentname= First_name+' '+Last_name FROM students_records WHERE studentid=@student_id

END

Note:-/* */ is used to put comments in one or multiple lines within your sql statements

--sign is used to write a comment in a single line

Note: It is not mandatory that a stored procedure will have to return something all the time. There may be a case when a stored procedure doesn't have to returns anything. For example, a stored procedure can be used to Insert, delete or update As a statement For example, the below created stored procedure is used to insert values into the table students_records.

/*

This Stored procedure is used to Insert value into the table tbl_students.

*/

Create Procedure InsertStudentrecord

(

 @StudentFirstName Varchar(200),

 @StudentLastName  Varchar(200),

 @StudentEmail     Varchar(50)

)

As

 Begin

   Insert into Students_records (First_name, last_name, Email)

   Values(@StudentFirstName, @StudentLastName,@StudentEmail)

 End

Execution of the Stored Procedure without having an Output Parameter

A stored procedure is used in the SQL Server with the help of "Execute" or "Exec" Keyword. Like, if we Have to execute the stored procedure "Getstudentname", then we have to use the below written statement.

Execute Getstudentname 1

Exec Getstudentname 1

Execution of the Stored Procedure using the Output Parameter

If we have to execute a stored procedure "GetstudentnameInOutputVariable" , then, first of all we  will have to declare a variable to collect the output values. Below is the example:

Declare @Studentname as nvarchar(200)   -- Declaring the variable to collect the Studentname

Declare @Studentemail as nvarchar(50)     -- Declaring the variable to collect the Studentemail

Exec GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output

select @Studentname,@Studentemail      -- "Select" Statement Has been used to show the output from Procedure created above--

Summary

At the end, we can say that a stored procedure does not enhances the possibility of reusing the code and execution plan only, but it increases the performance of the database as well by reducing the traffic of the network by reducing the amount of information that is sent over the network.