Using Stored Procedures in SQL Server Database

Using Stored Procedures in SQL Server Database

In this article, I am explaining How to write stored procedures in SQL Server 2000 / 2005 / 2008.

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.

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