Stored Procedure in SQL: What It Is, Types, Syntax & Examples

Estimated study time: 21 minutes. Reusable, precompiled blocks of SQL logic.

A stored procedure is a saved collection of one or more SQL statements that can be executed as a single unit whenever needed. Instead of sending the same set of queries from an application every time, you store the logic once in the database and simply call it by name.

Why Use Stored Procedures?

• Performance: SQL Server compiles and caches an execution plan, so repeated calls avoid recompiling the query.
• Reusability: Write the logic once and call it from multiple applications or scripts.
• Security: Users can be granted permission to execute a procedure without direct access to the underlying tables.
• Maintainability: Business logic lives in one place instead of being duplicated across application code.

Basic Syntax

CREATE PROCEDURE ProcedureName
  @Parameter1 DataType,
  @Parameter2 DataType
AS
BEGIN
  -- SQL statements
END;

Simple Example

CREATE PROCEDURE GetEmployeeByID
  @EmpID INT
AS
BEGIN
  SELECT * FROM Employees WHERE EmpID = @EmpID;
END;
GO

EXEC GetEmployeeByID @EmpID = 101;

Types of Stored Procedures

1. System Stored Procedures

Built into SQL Server, prefixed with sp_, used for administrative tasks like sp_helpdb or sp_rename.

2. User-Defined Stored Procedures

Created by developers to encapsulate custom business logic, such as the GetEmployeeByID example above.

3. Extended Stored Procedures

Allow SQL Server to call functions written in an external programming language, typically via a DLL. These are largely deprecated in favor of CLR integration.

4. CLR Stored Procedures

Written in a .NET language (like C#) and compiled into the .NET Common Language Runtime, useful for logic that's awkward to express in plain T-SQL.

Stored Procedures with Output Parameters

CREATE PROCEDURE GetEmployeeCount
  @DeptID INT,
  @Total INT OUTPUT
AS
BEGIN
  SELECT @Total = COUNT(*) FROM Employees WHERE DeptID = @DeptID;
END;
GO

DECLARE @Count INT;
EXEC GetEmployeeCount @DeptID = 2, @Total = @Count OUTPUT;
SELECT @Count;

Modifying and Dropping a Procedure

ALTER PROCEDURE GetEmployeeByID
  @EmpID INT
AS
BEGIN
  SELECT EmpID, EmpName FROM Employees WHERE EmpID = @EmpID;
END;

DROP PROCEDURE GetEmployeeByID;