Stored Procedure in SQL Server with Example

Definition:

A Stored Procedure is nothing but already written SQL  queries that are stored in the database. If you are using same Queries  over and over then put those queries into the Stored Procedure.After put the SQL Statements in the Stored Procedure then you can run the Stored Procedure from the database commands Environment.(Here we are using SQL Server as Database)

   stored procedure can be defined  as set of logical group of SQL statements or pre compiled one or more SQL Statements  to perform a specific task.

Advantages of Stored Procedure:

• The main Benefit of Stored Procedure,it saves the time since stored procedure are executed on the server side and perform set of actions before returning  the results to client side.
• By using Stored Procedure method we can reduce the network traffic because it allows  set of actions  to be executed(if we send SQL Query which is executing again and again  to the server through network ,the network might be disconnected ,therefore the expecting result does not return to client side.
• It improves the performance of the database(since set of actions executed at once)
• SQL Statements needs to be compiled every time whenever it sent for execution whereas Stored Procedure required only one compilation when stored procedure is created.

How to create Stored Procedure in SQL Server

Firstly we need to create a table called emp_details

CREATE TABLE emp_details
(
[emp_id] [int] Identity (1,1) NOT NULL,
[FirstName][nvchar](300) NOT NULL,
[LastName] [nvchar](300)  NULL,
[Emali] [nvchar](100) NULL
)

Here i am going to insert the data for the above structure of table:

insert into emp_details(FirstName,LastName,Email)values('lucky','raju','lucky&xyz.com')

insert into emp_details(FirstName,LastName,Email)values('akhil','rao','akki&xyz.com')

insert into emp_details(FirstName,LastName,Email)values('naga','raju','nag&xyz.com')

insert into emp_details(FirstName,LastName,Email)values('krishna','raju','krish&xyz.com')


Now we are doing in the first step creating Stored Procedure 

Syntax:

Create Procedure procedure-name
(
Input parameters,
Output Parameters(if need)
)
As
Begin
Sql statement is used in Stored Procedure
End

Here if you need to create Stored Procedure which you want return empname  whose emp_id is given as Input Parameter to the stored Procedure then it will look like as below

Example 1:

Create Procedure GetEmpname //GetEmpname is name of the Stored Procedure
{
@emp_id int // input parameter emp_id of the employee details table
)
As
Begin
Select firstName +'   '+LastName from emp_details WHERE Emp_id=@Emp_id;
End


Example 2:

Stored Procedure can be used to insert ,update or delete a SQL Statement. Suppose you want insert a value into the Emp_details table.

create procedure InsertEmprecord
(
@Empfirstname varchar(100),
@EmpLastname varchar(100),
@EmpEmail varchar(50)
As
Begin
Insert into emp_details(FirstName,LastName,Email)values('@Empfirstname',@EmpLastname,@EmpEmail)
End

Finally we will see how to execute Stored Procedure in SQL Server:

To Execute Stored Procedure in SQL Server we use "Exec" (or) "Execute" Keyword.

Suppose if you want execute a Stored Procedure name as " GetEmpname "then we have to write command as below
Exec GetEmpname
OR
Execute GetEmpname


Conclusion:

If same SQL Query is required to execute again and again so it takes lot of network traffic to sent information to the server.If you create Stored procedure it reduce the network traffic by reducing the amount of information sent over the network because all pre compiled one or more SQL Statements put into the Stored Procedure..


Also check the post: SQL Queries interview Questions and Answers for Freshers