Contents
Stored Procedures
Stored procedures are sets of SQL statements that are stored and executed on the database server. They can be used to encapsulate complex business logic, ensure data integrity, and improve performance by reducing network traffic and reusing SQL code.
1. Creating Stored Procedures
Definition: Creating stored procedures involves defining a sequence of SQL commands that perform a specific task. Stored procedures are stored in the database and can be invoked repeatedly.
Example:
CREATE PROCEDURE AddCustomer
@CustomerID int,
@Name varchar(255),
@Address varchar(255)
AS
BEGIN
INSERT INTO Customers (CustomerID, Name, Address)
VALUES (@CustomerID, @Name, @Address);
END;
This stored procedure, named AddCustomer, adds a new customer to the Customers table. It accepts three parameters: CustomerID, Name, and Address.
Usage Example:
EXEC AddCustomer 1, 'John Doe', '123 Elm Street';
This command executes the AddCustomer stored procedure, inserting a new customer into the database.
1.1 Executing Stored Procedures
Definition: Executing stored procedures refers to the process of calling a stored procedure that has been defined in the database.
Example:
EXEC AddCustomer @CustomerID = 2, @Name = 'Jane Smith', @Address = '456 Oak Street';
This example demonstrates how to execute the AddCustomer stored procedure using named parameters to insert a new customer into the database.
1.2 Parameters in Stored Procedures
Definition: Parameters in stored procedures are placeholders used to pass values to and from stored procedures. They allow stored procedures to be more dynamic and adaptable to different data inputs.
Example:
CREATE PROCEDURE UpdateCustomerAddress
@CustomerID int,
@NewAddress varchar(255)
AS
BEGIN
UPDATE Customers
SET Address = @NewAddress
WHERE CustomerID = @CustomerID;
END;
This stored procedure, named UpdateCustomerAddress, updates the address of a customer. It takes two parameters: CustomerID for identifying the customer and NewAddress for the new address.
Usage Example:
EXEC UpdateCustomerAddress @CustomerID = 1, @NewAddress = '789 Pine Street';
This command updates the address of the customer with CustomerID 1.
1.3 Error Handling in Stored Procedures
Definition: Error handling in stored procedures is crucial for managing exceptions and ensuring that operations in the database proceed smoothly. SQL Server provides several mechanisms for error handling, such as TRY...CATCH blocks.
Example:
CREATE PROCEDURE DeleteCustomer
@CustomerID int
AS
BEGIN
BEGIN TRY
DELETE FROM Customers
WHERE CustomerID = @CustomerID;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END;
This stored procedure attempts to delete a customer from the Customers table. If an error occurs during the deletion (for example, if the customer does not exist), the error is caught, and the error details are returned.
Usage Example:
EXEC DeleteCustomer @CustomerID = 3;
This command attempts to delete a customer. If the customer does not exist, it catches the error and returns the error details.
Detailed Example with Explanations
Let’s consider a practical scenario where we use stored procedures to manage customer data in a Customers table.
Step 1: Creating the Customers Table
CREATE TABLE Customers (
CustomerID int PRIMARY KEY,
Name varchar(255),
Address varchar(255)
);
Step 2: Defining a Stored Procedure for Adding Customers
CREATE PROCEDURE AddCustomer
@CustomerID int,
@Name varchar(255),
@Address varchar(255)
AS
BEGIN
INSERT INTO Customers (CustomerID, Name, Address)
VALUES (@CustomerID, @Name, @Address);
END;
Step 3: Using the Stored Procedure
EXEC AddCustomer @CustomerID = 1, @Name = 'Alice Wonder', @Address = '100 Main St';
Step 4: Handling Errors
Imagine we need to handle cases where duplicate CustomerID entries might cause an error. We could enhance the AddCustomer procedure with error handling:
ALTER PROCEDURE AddCustomer
@CustomerID int,
@Name varchar(255),
@Address varchar(255)
AS
BEGIN
BEGIN TRY
INSERT INTO Customers (CustomerID, Name, Address)
VALUES (@CustomerID, @Name, @Address);
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END;
Step 5: Testing Error Handling
-- Trying to insert a customer with an existing ID
EXEC AddCustomer @CustomerID = 1, @Name = 'Bob Vila', @Address = '101 Main St';
This structured approach to managing customer data via stored procedures not only encapsulates business logic but also enhances data management efficiency and robustness. Through examples and detailed explanations, this guide provides a thorough understanding of creating, executing, and managing stored procedures in SQL.