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.
