SQL Stored Procedures for SQL Server

Introduction to SQL Server Stored Procedures

A stored procedure is a precompiled collection of one or more SQL statements that can be executed on demand. In SQL Server, stored procedures are used to encapsulate repetitive operations, enforce business logic, improve performance, and increase security by limiting direct access to underlying tables.

Here’s an overview of how to create, manage, and call stored procedures in SQL Server.

Creating a Basic Stored Procedure

To create a stored procedure in SQL Server, use the CREATE PROCEDURE statement. Here's an example:

CREATE PROCEDURE GetEmployeeInfo
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees;
END;

This simple stored procedure returns a list of employees from the Employees table when executed.

Stored Procedure with Parameters

Stored procedures can also accept parameters, making them more flexible for different use cases.

Example with input parameters

CREATE PROCEDURE GetEmployeeById
    @EmployeeID INT
AS
BEGIN
    SELECT FirstName, LastName, Department
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

Here, the procedure accepts an EmployeeID as an input parameter and returns the employee's details.

Calling a Stored Procedure

You can execute a stored procedure using the EXEC (or EXECUTE) statement.

To call the GetEmployeeById procedure and pass a parameter:

EXEC GetEmployeeById @EmployeeID = 123;

Or:

EXEC GetEmployeeById 123;

Stored Procedure with Output Parameters

You can also define output parameters in a stored procedure to return values to the caller.

Example:

CREATE PROCEDURE GetEmployeeCount
    @DepartmentName NVARCHAR(100),
    @EmployeeCount INT OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*)
    FROM Employees
    WHERE Department = @DepartmentName;
END;

To call this procedure and get the result in an output variable:

DECLARE @EmpCount INT;
EXEC GetEmployeeCount @DepartmentName = 'HR', @EmployeeCount = @EmpCount OUTPUT;
SELECT @EmpCount AS EmployeeCount;

Updating Data with a Stored Procedure

You can create stored procedures that update data in your tables.

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(10, 2)
AS
BEGIN
    UPDATE Employees
    SET Salary = @NewSalary
    WHERE EmployeeID = @EmployeeID;
END;

To execute this procedure and update an employee's salary:

EXEC UpdateEmployeeSalary @EmployeeID = 123, @NewSalary = 75000.00;

Error Handling in Stored Procedures

SQL Server supports error handling using TRY...CATCH blocks.

Example with error handling:

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

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

        -- Check if the employee exists
        IF @@ROWCOUNT = 0
        BEGIN
            THROW 50000, 'Employee not found', 1;
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SELECT ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END;

In this example:

• We use a transaction to ensure that the update is atomic.
• If the employee doesn't exist, we raise a custom error using the THROW statement.
• If there’s any issue, the transaction is rolled back, and the error message is returned.

Dropping a Stored Procedure

If you need to remove a stored procedure, use the DROP PROCEDURE statement.

DROP PROCEDURE GetEmployeeInfo;

Altering an Existing Stored Procedure

If you need to modify an existing procedure, use the ALTER PROCEDURE statement.

ALTER PROCEDURE GetEmployeeById
    @EmployeeID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department, Salary
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

Executing Dynamic SQL in Stored Procedures

If you need to execute dynamic SQL (SQL that is built at runtime), you can use sp_executesql or EXEC.

Example with sp_executesql:

CREATE PROCEDURE GetEmployeesByDept
    @Department NVARCHAR(100)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'SELECT EmployeeID, FirstName, LastName FROM Employees WHERE Department = @Dept';
    
    EXEC sp_executesql @SQL, N'@Dept NVARCHAR(100)', @Department;
END;

In this case, the procedure takes a department name and executes a dynamically constructed SQL query.

Best Practices

• Avoid excessive complexity: If a procedure is too complex, consider breaking it down into smaller, manageable pieces.
• Use transactions wisely: For operations that modify data, ensure transactions are used to maintain consistency.
• Parameter validation: Always validate the input parameters to avoid unexpected behavior or SQL injection risks.
• Error handling: Use TRY...CATCH for robust error handling.
• Optimize performance: Make sure the stored procedures are optimized for performance. Avoid unnecessary SELECT *, and index frequently queried columns.

Conclusion

Stored procedures are a powerful feature in SQL Server that can help with modularizing and optimizing your SQL code. They provide a means to enhance security, performance, and maintainability of your database interactions.