<aside> ⚙️ Stored procedures are saved SQL code blocks that can be executed by name. They support parameters, variables, and error handling.

</aside>

1. Basic Stored Procedure

-- Create
CREATE PROCEDURE GetCustomerSummary AS
BEGIN
    SELECT COUNT(*) AS TotalCustomers, AVG(Score) AS AvgScore
    FROM Sales.Customers
    WHERE Country = 'USA';
END
GO

-- Execute
EXEC GetCustomerSummary;

2. Parameters

ALTER PROCEDURE GetCustomerSummary
    @Country NVARCHAR(50) = 'USA'  -- default value
AS
BEGIN
    SELECT COUNT(*) AS TotalCustomers, AVG(Score) AS AvgScore
    FROM Sales.Customers
    WHERE Country = @Country;
END
GO

-- Execute with different parameters
EXEC GetCustomerSummary @Country = 'Germany';
EXEC GetCustomerSummary @Country = 'USA';
EXEC GetCustomerSummary;  -- uses default 'USA'

3. Variables

ALTER PROCEDURE GetCustomerSummary @Country NVARCHAR(50) = 'USA' AS
BEGIN
    DECLARE @TotalCustomers INT, @AvgScore FLOAT;

    SELECT @TotalCustomers = COUNT(*), @AvgScore = AVG(Score)
    FROM Sales.Customers WHERE Country = @Country;

    PRINT('Total: ' + CAST(@TotalCustomers AS NVARCHAR));
    PRINT('Avg Score: ' + CAST(@AvgScore AS NVARCHAR));
END
GO

4. IF/ELSE Control Flow

ALTER PROCEDURE GetCustomerSummary @Country NVARCHAR(50) = 'USA' AS
BEGIN
    IF EXISTS (SELECT 1 FROM Sales.Customers WHERE Score IS NULL AND Country = @Country)
    BEGIN
        PRINT('Updating NULL scores to 0');
        UPDATE Sales.Customers SET Score = 0
        WHERE Score IS NULL AND Country = @Country;
    END
    ELSE
    BEGIN
        PRINT('No NULL scores found');
    END;
END
GO

5. TRY/CATCH Error Handling

ALTER PROCEDURE GetCustomerSummary @Country NVARCHAR(50) = 'USA' AS
BEGIN
    BEGIN TRY
        -- Your SQL here...
        SELECT COUNT(*) FROM Sales.Customers WHERE Country = @Country;
    END TRY
    BEGIN CATCH
        PRINT('Error: ' + ERROR_MESSAGE());
        PRINT('Line: '  + CAST(ERROR_LINE() AS NVARCHAR));
    END CATCH;
END
GO

Benefits of Stored Procedures