Stored Procedure:- Stored Procedure In Sql server can be defined as the set of logically group of sql statement which are grouped to perform a specific task. There are many benefits of using a stored procedure. The main benefit of using a stored procedure is that it increases the performance of the database.The other benefits of using the Stored Procedure is given below:-
Benefits of using the Stored Procedure:-
1) One of the main benefit of using the Stored procedure is that it reduces the amount of information sent to the database server. It can become more important benefit when the bandwidth of the network is less. Since if we send the sql query (statement)which is executing in a loop to the server through network and the network get disconnected then the execution of the sql statement don’t returns the expected results, if the sql query is not used between Transaction statement and rollback statement is not used.
2) Compilation step is required only once when the stored procedure is created. Then after it does not required recompilation before executing unless it is modified and re utilizes the same execution plan whereas the sql statements needs to be compiled every time whenever it is sent for execution even if we send the same sql statement every time.
3) It helps in re usability of the sql code because it can be used by multiple users and by multiple client since we needs to just call the stored procedure instead of writing the same sql statement every time. It helps in reduces the development time.
4) Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving the permission on the tables used in the Stored procedure.
5) Sometime it is useful to use the database for storing the business logic in the form of stored procedure since it make it secure and if any change is needed in the business logic then we may only need to make changes in the stored procedure and not in the files contained on the web server.
How to write a Stored Procedure in Sql server:-
Suppose there is a table called tbl_Students whose structure is given below:-
CREATE TABLE tbl_Students
(
[Studentid] [int] IDENTITY(1,1) NOT NULL,
[Firstname] [nvarchar](200) NOT NULL,
[Lastname] [nvarchar](200) NULL,
[Email] [nvarchar](100) NULL
)
Support we insert the following data into the above table:-
Insert into tbl_Students (Firstname, lastname, Email)
Values(‘Vivek’, ‘Johari’, ‘vivek@abc.com’)Insert into tbl_Students (Firstname, lastname, Email)
Values(‘Pankaj’, ‘Kumar’, ‘pankaj@abc.com’)
Insert into tbl_Students (Firstname, lastname, Email)
Values(‘Amit’, ‘Singh’, ‘amit@abc.com’)
Insert into tbl_Students (Firstname, lastname, Email)
Values(‘Manish’, ‘Kumar’, ‘manish@abc.comm’)
Insert into tbl_Students (Firstname, lastname, Email)
Values(‘Abhishek’, ‘Singh’, ‘abhishek@abc.com’)
Now, while writing a Stored Procedure, the first step will be to write the Create Procedure statement as the first statement,
Create Procedure Procedure-name
(
Input parameters ,
Output Parameters (If required)
)
As
Begin
Sql statement used in the stored procedure
End
Now, suppose we need to create a Stored Procedure which will returns a student name whose studentid is given as the input parameter to the stored procedure. Then the Stored Procedure will be:-
/* Getstudentname is the name of the stored procedure*/
Create PROCEDURE Getstudentname
(
@studentid INT –Input parameter , Studentid of the student
)
AS
BEGIN
SELECT Firstname+’ ‘+Lastname FROM tbl_Students WHERE studentid=@studentid
END
We can also collect the student name in the output parameter of the Stored Procedure.For Example:-
/*
GetstudentnameInOutputVariable is the name of the stored procedure 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= Firstname+’ ‘+Lastname FROM tbl_Students WHERE studentid=@studentid
END
Note:-/* */ is used to write comments in one or multiple lines
— is used to write comment in a single line
How to Alter a Stored Procedure in a Sql server:-
In Sql Server, a stored procedure can be modified with the help of the Alter keyword.Now if, we want to get student email address through the same procedure GetstudentnameInOutputVariable . So we need to modified it by adding one more output parameter ” @StudentEmail ” which is shown below:-
/*
Stored Procedure GetstudentnameInOutputVariable is modified to collect the email address of the student with the help of the Alert Keyword
*/
Alter PROCEDURE GetstudentnameInOutputVariable
(@studentid INT, –Input parameter , Studentid of the student
@studentname VARCHAR (200) OUT, — Output parameter to collect the student name
@StudentEmail VARCHAR (200)OUT — Output Parameter to collect the student email
)
AS
BEGIN
SELECT @studentname= Firstname+’ ‘+Lastname, @StudentEmail=email FROM tbl_Students WHERE studentid=@studentid
END
Note:- It is not necessary that a stored procedure will have to written. It can be the case when a stored procedure doesn’t written any thing. For Example, a stored procedure can be used to Insert, delete or update a sql statement. For Example the below stored procedure is used tp insert value into the table tbl_students.
/*
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 tbl_Students (Firstname, lastname, Email)
Values(@StudentFirstName, @StudentLastName,@StudentEmail)
End
Execution of the Stored Procedure in Sql server:-
Execution of the Stored Procedure which don’t have a Output Parameter:-
A stored procedure is used in the Sql server with the help of the “Execute” or “Exec” Keyword. For Example , If we want to execute the stored procedure “Getstudentname”, then we will use the following statement:-
Execute Getstudentname 1
Exec Getstudentname 1
Execution of the Stored Procedure using the Output Parameter:-
If we want to execute the Stored procedure “GetstudentnameInOutputVariable ” , then we first need to declare the variable to collect the output values. For 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
Execute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output
select @Studentname,@Studentemail — “Select” Statement is used to show the output from Procedure
Summary:-
In the end we can say that a Stored procedure not only enhancing the possibility of reusing the code and execution plan, but it also increases the performance of the database by reducing the traffic of the network by reducing the amount of information send over the network.

good post.. very helpful.. thanks
Thanks CJ, for your valuable comment:-) It will really motivate me to share my knowledge with more posts.
realy nice and helpful..
thanks
very very useful for beginners.. simple and easy to understand.. these kinds of info means a lot to a beginner like me…
Very helpul
Thanks
Thanks for this article. You have explained it very nicely. I came to know about this blog through your article in the CodeProject. You have great many articles in you blog. Expects more and more article from you.
Thanks Pramod for your valuable comments
Nice Post, thank you very much for sharing.
sir awesome
Difficult to understand Trigger could u help me with this
This article will help you in understanding the triggers
http://www.techmixing.com/2018/12/sql-triggers-introduction.html
Very useful. Thanks for the detailed explanation.
Keep it up Vivek
bermuda topper league 17 preto
tenis asics challenger gel masculino barato. carregando zoomcamisa flamengo i player 18 19 roupas e cal莽ados henrique jorget锚nis adidas superstar 80s decon masculinochuteira society asics artista wd chuteiras laranja no mercado
… [Trackback]
[…] Info on that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
… [Trackback]
[…] There you can find 65610 more Information to that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
… [Trackback]
[…] Find More on on that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
… [Trackback]
[…] Information on that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
… [Trackback]
[…] Info on that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
… [Trackback]
[…] Read More Information here to that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
… [Trackback]
[…] Read More to that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
… [Trackback]
[…] Find More on on that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
… [Trackback]
[…] Information to that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
… [Trackback]
[…] Information to that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
… [Trackback]
[…] Find More on on that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
… [Trackback]
[…] Find More here on that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
… [Trackback]
[…] Information on that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
… [Trackback]
[…] Read More here on that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
… [Trackback]
[…] Information to that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
nike air max 2017
There are definitely quite a lot of particulars like that to take into consideration. That could be a great point to carry up. I offer the ideas above as basic inspiration however clearly there are questions like the one you carry up where a very power…
[…] Read 80394 More here on that Topic: techmixing.com/2009/12/sql-server-how-to-write-stored.html […]
jordan 11
After research a number of of the blog posts in your web site now, and I really like your method of blogging. I bookmarked it to my bookmark website checklist and can be checking again soon. Pls try my site as properly and let me know what you think.
nike shox for women
Aw, this was a very nice post. In idea I would like to put in writing like this additionally ?taking time and precise effort to make a very good article?however what can I say?I procrastinate alot and certainly not seem to get something done.
louboutin shoes
Can I simply say what a aid to search out somebody who really knows what theyre talking about on the internet. You definitely know the right way to convey an issue to mild and make it important. More individuals must read this and understand this side…
supreme clothing
There are certainly a whole lot of details like that to take into consideration. That could be a nice level to convey up. I offer the thoughts above as general inspiration however clearly there are questions like the one you convey up the place a very…
louboutin shoes
This really answered my downside, thanks!
adidas yeezy
There is noticeably a bundle to find out about this. I assume you made certain nice factors in options also.
reebok chaussures sportif sneakers royal glide blanc ripple clip 10 10 sur 11
dolls kill 銉堛儍銉椼偣銇濄伄浠?dolls kill current mood 銉曘偅銉冦偡銉ャ儘銉冦儓銈枫偄銉笺儓銉冦儣銈?/a>銉嬨儱銉笺儛銉┿兂銈?metro 銈广儓銉儍銉侀箍銇瓙銉€銉炽儨銉笺儷 銉併偋銉冦偒銉笺儣銉兂銉堛偔銉ャ儹銉冦儓銉戙兂銉?銉溿儓銉?銉兂銉斻兗銈?銉儑銈c兗銈?銈淬儷銉曘偊銈с偄 銈枫儱銉笺偤 銈淬儷銉曠敤鍝?婵€瀹?/a>銉撱偢銉嶃偣銈枫儱銉笺偤 銉°兂銈?澶с亶銇勩偟銈ゃ偤瀵惧繙 24.5 30.0cm 绂忚 20绋銇嬨倝閬搞伖銈?/a>…
movies
movies
separador plata pandora
dsquared2 womens sandals shopstylefake puma selena gomez sneakerspenneys have just released knock off balenciagas for next to60 off buy 2019 maison margiela replica painter shoe in white mix
http://wwww.aicom.com/wp-content/_input_3_Tips_on_How_to_Formulate_the_Thesis_Structure_Properly.html
Nice and informative post. Thank you for sharing.
[…] SQL Stored Procedure […]