Stored Procedures Examples

This section provides practical examples of creating and using stored procedures in SQL Server. Stored procedures are precompiled SQL statements that can be executed repeatedly, offering benefits such as improved performance, enhanced security, and modular code design.

1. Simple Stored Procedure to Select Data

This example demonstrates a basic stored procedure that retrieves all records from a hypothetical Employees table.

CREATE PROCEDURE usp_GetAllEmployees
AS
BEGIN
    SET NOCOUNT ON; -- Prevents the count of rows affected from being returned
    SELECT EmployeeID, FirstName, LastName, HireDate
    FROM Employees;
END;
GO
                

EXEC usp_GetAllEmployees;
GO
                

2. Stored Procedure with Input Parameters

This procedure selects employees based on a provided DepartmentID.

CREATE PROCEDURE usp_GetEmployeesByDepartment
    @DepartmentID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT EmployeeID, FirstName, LastName, HireDate
    FROM Employees
    WHERE DepartmentID = @DepartmentID;
END;
GO
                

EXEC usp_GetEmployeesByDepartment @DepartmentID = 5; -- Get employees from department 5
GO
                

3. Stored Procedure with Output Parameters

This procedure returns a count of employees in a specific department using an output parameter.

CREATE PROCEDURE usp_CountEmployeesByDepartment
    @DepartmentID INT,
    @EmployeeCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @EmployeeCount = COUNT(*)
    FROM Employees
    WHERE DepartmentID = @DepartmentID;
END;
GO
                

DECLARE @Count INT;
EXEC usp_CountEmployeesByDepartment @DepartmentID = 3, @EmployeeCount = @Count OUTPUT;
SELECT 'Number of employees in department 3:', @Count AS TotalEmployees;
GO
                

4. Stored Procedure with Return Values

While output parameters are generally preferred for returning data, return values can be used to indicate status or success/failure.

CREATE PROCEDURE usp_AddNewEmployee
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @DepartmentID INT,
    @HireDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    -- Basic validation
    IF EXISTS (SELECT 1 FROM Departments WHERE DepartmentID = @DepartmentID)
    BEGIN
        INSERT INTO Employees (FirstName, LastName, DepartmentID, HireDate)
        VALUES (@FirstName, @LastName, @DepartmentID, @HireDate);
        RETURN 0; -- Indicate success
    END
    ELSE
    BEGIN
        RETURN 1; -- Indicate failure (invalid department)
    END
END;
GO
                

DECLARE @ReturnStatus INT;
EXEC @ReturnStatus = usp_AddNewEmployee
    @FirstName = 'Jane',
    @LastName = 'Doe',
    @DepartmentID = 10, -- Assuming DepartmentID 10 does not exist for failure demonstration
    @HireDate = '2023-10-27';

IF @ReturnStatus = 0
    PRINT 'Employee added successfully.';
ELSE
    PRINT 'Failed to add employee. Invalid DepartmentID.';
GO
                

5. Stored Procedure with Dynamic SQL

This procedure demonstrates how to build and execute dynamic SQL. Use with caution to prevent SQL injection.

CREATE PROCEDURE usp_SearchEmployees
    @SearchTerm NVARCHAR(100),
    @SearchField NVARCHAR(50) -- e.g., 'FirstName', 'LastName'
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX);

    -- Basic validation for search field to prevent SQL injection
    IF @SearchField NOT IN ('FirstName', 'LastName')
    BEGIN
        RAISERROR('Invalid search field. Only FirstName and LastName are allowed.', 16, 1);
        RETURN;
    END

    SET @SQL = N'SELECT EmployeeID, FirstName, LastName, HireDate
                 FROM Employees
                 WHERE ' + QUOTENAME(@SearchField) + N' LIKE ''%' + REPLACE(@SearchTerm, '''', '''''') + '%'';';

    EXEC sp_executesql @SQL;
END;
GO
                

EXEC usp_SearchEmployees @SearchTerm = 'John', @SearchField = 'FirstName';
EXEC usp_SearchEmployees @SearchTerm = 'Smith', @SearchField = 'LastName';
GO
                

Explore further to learn about creating, modifying, and managing stored procedures effectively.