Microsoft Docs

Stored Procedures Overview

What is a Stored Procedure?

A stored procedure is a pre‑compiled collection of one or more T‑SQL statements that are stored under a name and executed as a unit. Stored procedures encapsulate business logic, improve performance by reducing network round‑trips, and help enforce security and data integrity.

CREATE PROCEDURE dbo.usp_GetEmployeeDetails
    @EmployeeID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT FirstName, LastName, Title
    FROM dbo.Employee
    WHERE EmployeeID = @EmployeeID;
END
GO

Creating a Stored Procedure

Use the CREATE PROCEDURE statement. Common options include:

  • WITH ENCRYPTION – encrypts the definition.
  • WITH RECOMPILE – forces recompilation each execution.
  • AS BEGIN … END – logical block for the procedure body.

Save your definition with the GO batch terminator.

Executing a Stored Procedure

Call a stored procedure using EXEC or simply the procedure name.

EXEC dbo.usp_GetEmployeeDetails @EmployeeID = 5;
-- or
dbo.usp_GetEmployeeDetails 5;

Procedures can return result sets, output parameters, and return codes.

Parameters

Procedures support input, output, and input‑output parameters.

CREATE PROCEDURE dbo.usp_GetOrders
    @CustomerID nchar(5),
    @TotalOrders int OUTPUT
AS
BEGIN
    SELECT @TotalOrders = COUNT(*)
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID;
END
GO

Invoke with OUTPUT keyword to retrieve values.

Best Practices

  • Keep procedures focused on a single task.
  • Prefer schema‑qualified names (e.g., dbo.ProcedureName).
  • Avoid dynamic SQL unless necessary; use parameters to prevent injection.
  • Document input/output parameters clearly.
  • Use TRY…CATCH for error handling and set appropriate transaction scopes.