Create Store Procedure in Sql Server

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.

Create Procedure

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

Execute the Procedure

exec Procedure name

Alter or Modify a Stored Procedure

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

 DROP keyword is used proceeded by the name of the stored procedure.

Example

DROP PROCEDURE GetEmployeeDetails