SQL Stored Procedures

Stored procedures are a set of SQL statements that are compiled and stored on the database server. They can be executed on demand by applications or other SQL statements.

Using stored procedures offers several advantages:

Performance: Procedures are compiled once and stored in the database. Subsequent calls are faster as the execution plan is already available.
Reusability: Common logic can be encapsulated in a procedure and called from multiple places, reducing code duplication.
Security: Permissions can be granted to execute a procedure without granting direct access to the underlying tables.
Maintainability: Changes to database logic can be made in a single place (the procedure) without affecting multiple application codebases.
Reduced Network Traffic: Instead of sending multiple SQL statements, only the procedure name and its parameters need to be sent over the network.

Creating a Stored Procedure

The syntax for creating a stored procedure varies slightly between different SQL database systems (e.g., SQL Server, PostgreSQL, MySQL). Here's a general example using T-SQL (SQL Server):

Example: Creating a simple Stored Procedure


CREATE PROCEDURE GetEmployeeCountByDepartment
    @DepartmentName NVARCHAR(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT COUNT(*) AS EmployeeCount
    FROM Employees
    WHERE Department = @DepartmentName;
END;

Key Components:

CREATE PROCEDURE procedure_name: Initiates the creation of a procedure.
@parameter_name data_type: Defines input parameters for the procedure.
AS BEGIN ... END: Encloses the body of the procedure, containing the SQL statements.
SET NOCOUNT ON;: A common practice to prevent the count of rows affected by SQL statements from being returned as part of the result set, which can improve performance and avoid confusion.

Executing a Stored Procedure

Stored procedures are executed using the EXEC or EXECUTE command.

Example: Executing the Stored Procedure


-- Execute the procedure to get the count for the 'Sales' department
EXEC GetEmployeeCountByDepartment @DepartmentName = 'Sales';

-- Another way to execute (if parameters are in order)
EXECUTE GetEmployeeCountByDepartment 'Marketing';

Modifying and Dropping Stored Procedures

You can modify an existing stored procedure using ALTER PROCEDURE or drop it entirely with DROP PROCEDURE.

Example: Altering and Dropping


-- Alter an existing procedure
ALTER PROCEDURE GetEmployeeCountByDepartment
    @DepartmentName NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT COUNT(EmployeeID) AS NumberOfEmployees
    FROM Employees
    WHERE Department = @DepartmentName;
END;

-- Drop a procedure
DROP PROCEDURE GetEmployeeCountByDepartment;

Advanced Concepts

Return Values: Procedures can return status codes or scalar values.
Output Parameters: Procedures can return multiple values through output parameters.
Error Handling: Using TRY...CATCH blocks for robust error management.
Dynamic SQL: Constructing SQL statements within a procedure programmatically (use with caution due to security risks like SQL injection).

Important Considerations

While powerful, stored procedures can make debugging and version control more complex if not managed properly. Always consider the trade-offs and follow best practices for code organization and security.

Explore the following topics for more detailed information:

MSDN Documentation