Stored Procedures

Stored procedures are a set of one or more Transact-SQL statements that are compiled and stored on the database server. They can be executed by name, making them a powerful tool for encapsulating complex logic, improving performance, and enhancing security.

What are Stored Procedures?

A stored procedure is a precompiled collection of Transact-SQL statements that is stored in the database. When a stored procedure is called, the database engine executes the statements within it. This contrasts with ad-hoc queries, where statements are sent to the server individually each time they are run.

Benefits of Using Stored Procedures

Performance: Stored procedures are compiled and execution plans are cached by the database. This means subsequent executions can be faster as the database doesn't need to recompile the query.
Reduced Network Traffic: Instead of sending multiple SQL statements over the network, you only send the name of the stored procedure and its parameters.
Reusability: Encapsulate common tasks into a single procedure that can be called from multiple applications or scripts.
Modularity: Break down complex operations into smaller, manageable stored procedures.
Security: Granting execute permissions on a stored procedure is often more secure than granting direct permissions to underlying tables, as it limits what users can do.
Maintainability: When logic needs to change, you only need to update the stored procedure in one place, rather than across multiple applications.

Creating a Stored Procedure

You can create a stored procedure using the CREATE PROCEDURE statement. Here's a simple example that retrieves all customers from a hypothetical 'Customers' table:


CREATE PROCEDURE GetAllCustomers
AS
BEGIN
    SELECT CustomerID, CompanyName, ContactName
    FROM Customers;
END;

Executing a Stored Procedure

Once created, a stored procedure can be executed using the EXECUTE or EXEC command:


EXEC GetAllCustomers;

Stored Procedures with Parameters

Stored procedures can accept input parameters, allowing for dynamic execution. They can also return output parameters or return values.

Example of a stored procedure with an input parameter:


CREATE PROCEDURE GetCustomerByID
    @CustomerID INT
AS
BEGIN
    SELECT CustomerID, CompanyName, ContactName, City
    FROM Customers
    WHERE CustomerID = @CustomerID;
END;

Executing the procedure with a parameter:


EXEC GetCustomerByID @CustomerID = 10;

Error Handling and Control Flow

Stored procedures support Transact-SQL's control flow statements (IF, WHILE, CASE) and error handling mechanisms (TRY...CATCH), enabling robust and complex logic.

Important Note: Stored procedure syntax and features can vary slightly between different database systems (e.g., SQL Server, MySQL, PostgreSQL). The examples here are generally applicable to SQL Server.

Next Steps

Explore Triggers to understand how to automatically execute stored procedures in response to data modifications, or delve into SQL Server Stored Procedures Reference for detailed syntax and options.