SQL Stored Procedures: Complete Guide
with Examples
What is a Stored Procedure?
A stored procedure is a prepared SQL code that you can save and reuse. It's like a function in
programming languages - you can pass parameters, perform operations, and return results.
Advantages of Stored Procedures
Performance: Pre-compiled and stored in database
Security: Control data access through procedures
Maintainability: Centralize business logic
Reduced Network Traffic: Execute multiple statements in one call
Basic Syntax
sql
CREATE PROCEDURE procedure_name
@parameter1 datatype,
@parameter2 datatype
AS
BEGIN
-- SQL statements
END
GO
Example 1: Simple Stored Procedure
sql
-- Create a simple procedure
CREATE PROCEDURE GetAllEmployees
AS
BEGIN
SELECT * FROM Employees;
END
GO
-- Execute the procedure
EXEC GetAllEmployees;
Example 2: Procedure with Parameters
sql
-- Create procedure with parameters
CREATE PROCEDURE GetEmployeeByDepartment
@DepartmentName VARCHAR(50),
@MinSalary DECIMAL(10,2) = 30000 -- Default parameter
AS
BEGIN
SELECT e.*, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = @DepartmentName
AND e.Salary >= @MinSalary;
END
GO
-- Execute with parameters
EXEC GetEmployeeByDepartment @DepartmentName = 'IT', @MinSalary = 50000;
EXEC GetEmployeeByDepartment @DepartmentName = 'HR'; -- Uses default salary
Example 3: Procedure with Output Parameters
sql
-- Create procedure with output parameter
CREATE PROCEDURE GetEmployeeCountByDepartment
@DepartmentName VARCHAR(50),
@EmployeeCount INT OUTPUT
AS
BEGIN
SELECT @EmployeeCount = COUNT(*)
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = @DepartmentName;
END
GO
-- Execute and get output
DECLARE @Count INT;
EXEC GetEmployeeCountByDepartment @DepartmentName = 'IT', @EmployeeCount =
@Count OUTPUT;
SELECT @Count AS 'Number of IT Employees';
Example 4: Procedure with Conditional Logic
sql
CREATE PROCEDURE UpdateEmployeeSalary
@EmployeeID INT,
@PercentageIncrease DECIMAL(5,2),
@MaxSalary DECIMAL(10,2) = 100000
AS
BEGIN
DECLARE @CurrentSalary DECIMAL(10,2);
DECLARE @NewSalary DECIMAL(10,2);
-- Get current salary
SELECT @CurrentSalary = Salary FROM Employees WHERE EmployeeID =
@EmployeeID;
-- Calculate new salary
SET @NewSalary = @CurrentSalary * (1 + @PercentageIncrease/100);
-- Apply maximum salary limit
IF @NewSalary > @MaxSalary
SET @NewSalary = @MaxSalary;
-- Update salary
UPDATE Employees
SET Salary = @NewSalary
WHERE EmployeeID = @EmployeeID;
-- Return old and new salary
SELECT @CurrentSalary AS OldSalary, @NewSalary AS NewSalary;
END
GO
-- Execute
EXEC UpdateEmployeeSalary @EmployeeID = 101, @PercentageIncrease = 10,
@MaxSalary = 95000;
Example 5: Procedure with Error Handling
sql
CREATE PROCEDURE AddNewEmployee
@FirstName VARCHAR(50),
@LastName VARCHAR(50),
@Email VARCHAR(100),
@DepartmentID INT,
@Salary DECIMAL(10,2)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
-- Check if email already exists
IF EXISTS (SELECT 1 FROM Employees WHERE Email = @Email)
BEGIN
RAISERROR('Email already exists', 16, 1);
END
-- Insert new employee
INSERT INTO Employees (FirstName, LastName, Email, DepartmentID,
Salary, HireDate)
VALUES (@FirstName, @LastName, @Email, @DepartmentID, @Salary,
GETDATE());
-- Get the new employee ID
DECLARE @NewEmployeeID INT = SCOPE_IDENTITY();
COMMIT TRANSACTION;
SELECT @NewEmployeeID AS NewEmployeeID, 'Employee added successfully'
AS Status;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
'Failed to add employee' AS Status;
END CATCH
END
GO
-- Execute
EXEC AddNewEmployee
@FirstName = 'John',
@LastName = 'Doe',
@Email = 'john.doe@email.com',
@DepartmentID = 2,
@Salary = 55000;
Example 6: Procedure with Cursors (Advanced)
sql
CREATE PROCEDURE UpdateDepartmentBudgets
@PercentageIncrease DECIMAL(5,2)
AS
BEGIN
DECLARE @DeptID INT;
DECLARE @CurrentBudget DECIMAL(15,2);
DECLARE @NewBudget DECIMAL(15,2);
-- Declare cursor
DECLARE DeptCursor CURSOR FOR
SELECT DepartmentID, Budget FROM Departments;
OPEN DeptCursor;
FETCH NEXT FROM DeptCursor INTO @DeptID, @CurrentBudget;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Calculate new budget
SET @NewBudget = @CurrentBudget * (1 + @PercentageIncrease/100);
-- Update department budget
UPDATE Departments
SET Budget = @NewBudget
WHERE DepartmentID = @DeptID;
FETCH NEXT FROM DeptCursor INTO @DeptID, @CurrentBudget;
END
CLOSE DeptCursor;
DEALLOCATE DeptCursor;
SELECT 'All department budgets updated successfully' AS Status;
END
GO
-- Execute
EXEC UpdateDepartmentBudgets @PercentageIncrease = 5;
Modifying and Managing Procedures
sql
-- View procedure definition
sp_helptext 'GetEmployeeByDepartment';
-- Modify an existing procedure
ALTER PROCEDURE GetAllEmployees
AS
BEGIN
SELECT EmployeeID, FirstName, LastName, Email, DepartmentID, Salary
FROM Employees
ORDER BY LastName, FirstName;
END
GO
-- Drop a procedure
DROP PROCEDURE IF EXISTS GetAllEmployees;
Best Practices
1. Use meaningful names: sp_GetCustomerOrders instead of sp_proc1
2. Include error handling: Always use TRY-CATCH blocks
3. Use parameters: Avoid dynamic SQL when possible
4. Add comments: Document your procedures
5. Test thoroughly: Test with various input values
6. Consider performance: Use appropriate indexes
Common System Stored Procedures
sql
-- List all procedures in database
sp_stored_procedures;
-- Get procedure dependencies
sp_depends 'GetEmployeeByDepartment';
-- View procedure parameters
sp_help 'GetEmployeeByDepartment';
Stored procedures are powerful tools for encapsulating database logic, improving performance,
and enhancing security in your SQL applications.
