Stored procedures allow you to execute a set of SQL statements as a single unit, which can accept parameters and return results. This modular approach not only enhances code readability but also promotes reusability across different applications. In this article, we will explore how to create, execute, and manage stored procedures, along with some best practices to follow.

Creating a Stored Procedure

To create a stored procedure, you use the CREATE PROCEDURE statement. Below is a simple example that demonstrates how to create a stored procedure to retrieve employee details based on their department.

CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, JobTitle
    FROM Employees
    WHERE DepartmentID = @DepartmentID;
END;

Explanation

  • CREATE PROCEDURE GetEmployeesByDepartment: This line defines the name of the stored procedure.
  • @DepartmentID INT: This is a parameter that the procedure accepts, allowing you to filter employees by department.
  • The SELECT statement retrieves employee details from the Employees table where the DepartmentID matches the provided parameter.

Executing a Stored Procedure

Once a stored procedure is created, you can execute it using the EXEC or EXECUTE command. Here’s how to call the GetEmployeesByDepartment procedure we just created:

EXEC GetEmployeesByDepartment @DepartmentID = 3;

Result

This command will return a list of employees who belong to the department with ID 3.

Returning Results

Stored procedures can also return values. You can use the RETURN statement to return an integer value, or you can use output parameters to return more complex data. Here’s an example of a stored procedure that calculates the total salary for a specific department:

CREATE PROCEDURE GetTotalSalaryByDepartment
    @DepartmentID INT,
    @TotalSalary DECIMAL(10, 2) OUTPUT
AS
BEGIN
    SELECT @TotalSalary = SUM(Salary)
    FROM Employees
    WHERE DepartmentID = @DepartmentID;
END;

Executing with Output Parameters

To execute this stored procedure and capture the output, you would do the following:

DECLARE @Total DECIMAL(10, 2);
EXEC GetTotalSalaryByDepartment @DepartmentID = 3, @TotalSalary = @Total OUTPUT;
SELECT @Total AS TotalSalary;

Result

This will return the total salary of employees in department ID 3.

Best Practices for Stored Procedures

  1. Keep Procedures Focused: Each stored procedure should perform a single task or operation. This makes it easier to maintain and understand.
  1. Use Meaningful Names: Choose descriptive names for your stored procedures that clearly indicate their purpose, such as GetEmployeesByDepartment or UpdateEmployeeSalary.
  1. Parameter Validation: Always validate input parameters to avoid SQL injection attacks and ensure data integrity.
  1. Error Handling: Implement error handling using TRY...CATCH blocks to manage exceptions gracefully.
  1. Avoid Cursors: Whenever possible, use set-based operations instead of cursors, as they are generally more efficient.
  1. Comment Your Code: Add comments within your stored procedures to explain complex logic or important decisions.

Example: A Complex Stored Procedure

Here’s a more complex example that demonstrates the use of transactions and error handling. This procedure updates an employee's salary and logs the change into an audit table.

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE Employees
        SET Salary = @NewSalary
        WHERE EmployeeID = @EmployeeID;

        INSERT INTO SalaryAudit (EmployeeID, OldSalary, NewSalary, ChangeDate)
        VALUES (@EmployeeID, (SELECT Salary FROM Employees WHERE EmployeeID = @EmployeeID), @NewSalary, GETDATE());

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        RAISERROR(@ErrorMessage, 16, 1);
    END CATCH
END;

Explanation

  • The procedure starts a transaction to ensure that both the salary update and the audit log are treated as a single unit of work.
  • If an error occurs, the transaction is rolled back, and an error message is raised.

Conclusion

Stored procedures are a fundamental aspect of SQL programming that can significantly enhance your database application's performance and maintainability. By encapsulating logic within stored procedures, you can create modular, reusable code that adheres to best practices. Remember to keep your procedures focused, validate parameters, and implement error handling to ensure robust database operations.

Learn more with useful resources: