Stored Procedures
Stored procedures are a powerful feature in SQL Server that allows you to encapsulate a set of Transact-SQL statements into a single, executable unit. They offer benefits such as improved performance, enhanced security, and reusability.
What are Stored Procedures?
A stored procedure is a precompiled collection of one or more Transact-SQL statements stored in the database. When you execute a stored procedure, the database engine runs the SQL statements within it. This means the execution plan is often cached, leading to faster execution on subsequent calls.
Benefits of Stored Procedures
- Performance: Procedures are compiled once and stored in memory. Subsequent executions are faster as the query plan is reused.
- Security: You can grant execute permissions on a stored procedure without granting direct permissions on the underlying tables, offering fine-grained access control.
- Modularity and Reusability: Complex logic can be encapsulated, making it easier to maintain and reuse across different applications.
- Reduced Network Traffic: Instead of sending multiple SQL statements over the network, only the call to the stored procedure is sent.
- Maintainability: Changes to the underlying database schema can be made within the stored procedure without affecting client applications directly, as long as the procedure's interface remains the same.
Creating a Stored Procedure
The basic syntax for creating a stored procedure is:
CREATE PROCEDURE procedure_name
[ @parameter1 datatype = default_value,
@parameter2 datatype = default_value,
... ]
AS
BEGIN
-- SQL statements to be executed
SELECT column1, column2
FROM YourTable
WHERE some_column = @parameter1;
END;
Example: Creating a Simple Procedure
Let's create a procedure to retrieve customer information based on their ID:
CREATE PROCEDURE GetCustomerByID
@CustomerID INT
AS
BEGIN
SELECT CustomerID, FirstName, LastName, Email
FROM Customers
WHERE CustomerID = @CustomerID;
END;
Executing a Stored Procedure
You execute a stored procedure using the EXECUTE or EXEC command.
EXECUTE GetCustomerByID @CustomerID = 101;
-- Or
EXEC GetCustomerByID 101;
Parameters in Stored Procedures
Stored procedures can accept input parameters and return output parameters.
Input Parameters
As seen in the GetCustomerByID example, parameters are declared with an '@' prefix and a data type. They can have default values.
Output Parameters
Output parameters allow a stored procedure to return a value back to the caller.
CREATE PROCEDURE GetCustomerCountByCity
@CityName NVARCHAR(50),
@CustomerCount INT OUTPUT
AS
BEGIN
SELECT @CustomerCount = COUNT(*)
FROM Customers
WHERE City = @CityName;
END;
To execute this procedure and retrieve the output parameter:
DECLARE @Count INT;
EXEC GetCustomerCountByCity @CityName = 'London', @CustomerCount = @Count OUTPUT;
SELECT @Count AS NumberOfCustomers;
OUTPUT keyword is crucial when you want to capture the value returned by an output parameter.
Modifying and Dropping Stored Procedures
You can modify an existing stored procedure using ALTER PROCEDURE. To remove a stored procedure, use DROP PROCEDURE.
Altering a Procedure
ALTER PROCEDURE GetCustomerByID
@CustomerID INT,
@IncludeOrders BIT = 0 -- Added a new optional parameter
AS
BEGIN
SELECT CustomerID, FirstName, LastName, Email
FROM Customers
WHERE CustomerID = @CustomerID;
IF @IncludeOrders = 1
BEGIN
SELECT OrderID, OrderDate, TotalAmount
FROM Orders
WHERE CustomerID = @CustomerID;
END
END;
Dropping a Procedure
DROP PROCEDURE GetCustomerByID;
Common Stored Procedure Scenarios
- Data Entry and Updates: Encapsulating INSERT, UPDATE, or DELETE operations for consistency.
- Data Retrieval: Creating complex queries that can be easily invoked.
- Business Logic: Implementing business rules and validations.
- Batch Processing: Executing a sequence of operations.