SQL Stored Procedures

Overview

A stored procedure is a pre‑compiled collection of one or more SQL statements that can be executed as a unit. Stored procedures help encapsulate business logic, improve performance, and provide a security layer.

Syntax

CREATE PROCEDURE [ schema_name . ] procedure_name
    [ @parameter_name data_type [ = default ] [ OUT | OUTPUT ] ,... ]
AS
BEGIN
    -- T‑SQL statements
END

Parameters

Parameters allow you to pass values into and out of a stored procedure.

Input parameters – default direction.
Output parameters – use OUTPUT keyword.
Default values – assign a default with =.

Examples

Simple Procedure

CREATE PROCEDURE dbo.GetEmployee
    @EmpID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName
    FROM dbo.Employees
    WHERE EmployeeID = @EmpID;
END;

Procedure with Output Parameter

CREATE PROCEDURE dbo.GetEmployeeCount
    @DeptID INT,
    @EmpCount INT OUTPUT
AS
BEGIN
    SELECT @EmpCount = COUNT(*)
    FROM dbo.Employees
    WHERE DepartmentID = @DeptID;
END;

Best Practices

Use schema‑qualified names.
Prefer set‑based operations over loops.
Avoid dynamic SQL when possible.
Document parameters and expected result sets.
Grant the least privileges required.