How to make stored procedure in sql

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.

Write a Stored Procedure in SQL Server:

Suppose there is a table called  tbl_Employee  whose structure is given like this:

CREATE TABLE  tbl_Employee

(
    [Employeeid] [int] IDENTITY(1,1) NOT NULL,
    [Firstname] [nvarchar](200) NOT  NULL,
    [Lastname] [nvarchar](200)  NULL,
    [Email] [nvarchar](100)  NULL
)

And we insert the following data into the above table:-

Insert into tbl_Employee(Firstname, lastname, Email)
 Values('Rahul', 'Johari', 'rahul@abc.com')

Insert into tbl_Employee (Firstname, lastname, Email)
 Values('Nikhil', 'Kumar', 'nikhil@abc.com')

Insert into tbl_Employee(Firstname, lastname, Email)
 Values('Jitendra', 'Singh', 'jitendra@abc.com')

Insert into tbl_Employee(Firstname, lastname, Email)
 Values('Manish', 'Kumar', 'manishfmca2009@gmail.comm')

Insert into tbl_Employee(Firstname, lastname, Email)
 Values('Ajay', 'Singh', 'ajay@abc.com')

Now, while writing a Stored Procedure,

 the first step will be to write the Create Procedure statement

Create Procedure Procedure-name 
(
Input parameters ,
Output Parameters (If required)
)
As
Begin
  write sql query used in the stored procedure here
End

Create procedure for insert the record

Create Procedure InsertEmployeerecord
(
 @EmpFirstName Varchar(200),
 @EmpName  Varchar(200),
 @EmpEmail     Varchar(50)
) 
As
 Begin
   Insert into tbl_Employee(Firstname, lastname, Email)
   Values(@EmpFirstName, @EmpastName,@EmpEmail)
 End

Create procedure for select the record

Create Procedure SelectEmployeerecord
(

 @EmpEmail  Varchar(50)
) 
As
 Begin
   select * from tbl_Employee  where Email=@EmpEmail
 End

Execution of the Stored Procedure in SQL Server

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 "SelectEmployeerecord", then we will use the following statement.

Exec SelectEmployeerecord 'manishfmca2009@gmail.comm'

Execute InsertEmployeerecord 'Manish','Kumar','manishfmca2009@gmail.comm'

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.