What is a stored procedure?

A Stored Procedure is a collection or a group of T-SQL statements. Stored Procedures are a precompiled set of one or more statements that are stored together in the database. They reduce the network load because of the precompilation. We can create a Stored Procedure using the "Create proc" statement.

Why we use Stored Procedure

There are several reasons to use a Stored Procedure. They are a network load reducer and decrease execution time because they are precompiled. The most important use of a Stored Procedure is for security purposes. They can restrict SQL Injection. We can avoid SQL injection by use of a Stored Procedure.

How to create a Stored Procedure

CREATE PROCEDURE spEmployee   
AS   
BEGIN   
  
SELECT EmployeeId, Name, Gender, DepartmentName   
FROM tblEmployees   
INNER JOIN tblDepartments   
ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId   
END  

Advantages of using a Stored Procedure in SQL Server

• It is very easy to maintain a Stored Procedure and they are re-usable.
• The Stored Procedure retains the state of the execution plans.
• Stored Procedures can be encrypted and that also prevents SQL Injection Attacks