Stored Procedures

Overview

Stored procedures are precompiled collections of one or more SQL statements that perform a specific task. They offer performance benefits, modularity, and enhanced security.

Creating a Simple Stored Procedure

CREATE PROCEDURE dbo.GetEmployeeCount
AS
BEGIN
    SET NOCOUNT ON;
    SELECT COUNT(*) AS EmployeeCount FROM dbo.Employees;
END;

Executing the Procedure

EXEC dbo.GetEmployeeCount;

Parameters and Output

Procedures can accept input parameters and return output values.

CREATE PROCEDURE dbo.GetEmployeesByDept
    @DeptID INT,
    @Total INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM dbo.Employees WHERE DepartmentID = @DeptID;
    SELECT @Total = COUNT(*) FROM dbo.Employees WHERE DepartmentID = @DeptID;
END;

Calling with parameters:

DECLARE @Count INT;
EXEC dbo.GetEmployeesByDept @DeptID = 5, @Total = @Count OUTPUT;
SELECT @Count AS EmployeesInDept5;

Best Practices