In this article I will explain what is Stored Procedure in SQL Server, how to create (write) and use a Stored Procedure and finally the advantages of using Stored Procedure in SQL Server.
Stored Procedures are compatible in all SQL Server versions namely 2000, 2005, 2008, 2008R2, 2012 and 2014.
What is a Stored Procedure?
A Stored Procedure is a collection of SQL statements (queries) compiled together as a one unit. A Stored Procedure can contain multiple SQL statements such as SELECT, INSERT, UPDATE or DELETE.
Advantages of using a Stored Procedure
It is good to follow practice of using Stored Procedures as they are precompiled thus reduce the overhead of compiling each time.
Stored Procedures also help in preventing SQL Injections since parameters are used in it. You can refer my article on Parameterized queries to avoid SQL Injection Parameterized Queries ADO.Net
Other benefits include
1. Create once and call it N number of times
2. Reduce traffic since instead of whole query only stored procedure name is sent from front end
3. You can give selected users right to execute a particular stored procedure.
4. A Stored Procedure can be easily modified at a given point of time without modifying any code in the Application.
5. We can easily use Transaction within a Stored Procedure.
Creating a Stored Procedure
Below figure displays the syntax for creating a stored procedure. As you can see below to create a stored procedure CREATE keyword is used.
![What is Stored Procedure in SQL Server, how to create and use a Stored Procedure and its Advantages]()
Example
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetEmployeeDetails
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees WHERE EmployeeID=@EmployeeID
END
GO
Alter or Modify a Stored Procedure
Below figure displays the syntax for alter a stored procedure. As you can see below to modify a stored procedure ALTER keyword is used rest all remains the same.
![What is Stored Procedure in SQL Server, how to create and use a Stored Procedure and its Advantages]()
Example
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE GetEmployeeDetails
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees WHERE EmployeeID=@EmployeeID
END
GO
Drop or Delete a Stored Procedure
Figure below displays how to drop a stored procedure. As you can see below to delete a stored procedure DROP keyword is used proceeded by the name of the stored procedure.
![What is Stored Procedure in SQL Server, how to create and use a Stored Procedure and its Advantages]()
Example
DROP PROCEDURE GetEmployeeDetails
