What is Stored Procedure in SQL Server, how to create and use a Stored Procedure and its Advantages

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.

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.

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.

Example

DROP PROCEDURE GetEmployeeDetails