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.

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.

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.

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.

Let’s consider a practical scenario where we use stored procedures to manage customer data in a Customers table.

CREATE TABLE Customers (
    CustomerID int PRIMARY KEY,
    Name varchar(255),
    Address varchar(255)
);

CREATE PROCEDURE AddCustomer
    @CustomerID int,
    @Name varchar(255),
    @Address varchar(255)
AS
BEGIN
    INSERT INTO Customers (CustomerID, Name, Address)
    VALUES (@CustomerID, @Name, @Address);
END;

EXEC AddCustomer @CustomerID = 1, @Name = 'Alice Wonder', @Address = '100 Main St';

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;

-- 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.