Azure SQL Database Stored Procedures Samples

Overview

CRUD Operations

Business Logic

Performance Tuning

Introduction to Stored Procedures in Azure SQL Database

Stored procedures are precompiled SQL statements stored on the database server. They offer numerous benefits, including improved performance, enhanced security, and reusability. This page provides a collection of sample stored procedures designed to illustrate common patterns and best practices when working with Azure SQL Database.

Explore the different categories below to find examples relevant to your development needs. Each sample includes the T-SQL code and a brief explanation of its functionality.

Key Benefits:

Performance: Reduced network traffic and execution plan caching.
Security: Encapsulate logic and grant execute permissions without granting table access.
Maintainability: Centralize complex logic in the database.
Reusability: Call procedures from multiple applications.

Common CRUD Operations

These samples demonstrate how to implement Create, Read, Update, and Delete (CRUD) operations using stored procedures in Azure SQL Database.

Get Product by ID

Retrieves a single product record based on its unique identifier.

                    
CREATE PROCEDURE usp_GetProductByID
    @ProductID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ProductID, ProductName, ListPrice, SellStartDate
    FROM Production.Product
    WHERE ProductID = @ProductID;
END
                    
                

Add New Customer

Inserts a new customer record into the Customers table.

                    
CREATE PROCEDURE usp_AddCustomer
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Email NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Sales.Customer (FirstName, LastName, EmailAddress)
    VALUES (@FirstName, @LastName, @Email);
    SELECT SCOPE_IDENTITY() AS NewCustomerID;
END
                    
                

Update Product Price

Modifies the list price for a specified product.

                    
CREATE PROCEDURE usp_UpdateProductPrice
    @ProductID INT,
    @NewPrice MONEY
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE Production.Product
    SET ListPrice = @NewPrice
    WHERE ProductID = @ProductID;
    SELECT @@ROWCOUNT AS RowsAffected;
END
                    
                

Delete Order

Removes an order and its related details from the database.

                    
CREATE PROCEDURE usp_DeleteOrder
    @OrderID INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        DELETE FROM Sales.SalesOrderDetail
        WHERE OrderID = @OrderID;

        DELETE FROM Sales.SalesOrderHeader
        WHERE OrderID = @OrderID;

        COMMIT TRANSACTION;
        SELECT 1 AS SuccessFlag;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        SELECT 0 AS SuccessFlag;
        -- Consider logging the error here
    END CATCH
END
                    
                

Business Logic Implementation

Stored procedures are ideal for encapsulating complex business logic. These examples show how to implement common business scenarios.

Process New Order

Handles the creation of a new sales order, including order details and inventory checks.

                    
CREATE PROCEDURE usp_ProcessNewOrder
    @CustomerID INT,
    @OrderDate DATE,
    @OrderDetails StructuredProductList READONLY -- Requires a user-defined table type
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @NewOrderID INT;

    -- Validate Customer
    IF NOT EXISTS (SELECT 1 FROM Sales.Customer WHERE CustomerID = @CustomerID)
    BEGIN
        RAISERROR('Invalid Customer ID.', 16, 1);
        RETURN;
    END

    BEGIN TRANSACTION;
    BEGIN TRY
        -- Insert Order Header
        INSERT INTO Sales.SalesOrderHeader (CustomerID, OrderDate, Status)
        VALUES (@CustomerID, @OrderDate, 1); -- Status 1 = Draft
        SET @NewOrderID = SCOPE_IDENTITY();

        -- Insert Order Details and Check Inventory
        INSERT INTO Sales.SalesOrderDetail (OrderID, ProductID, OrderQty, UnitPrice)
        SELECT @NewOrderID, ProductID, OrderQty, UnitPrice
        FROM @OrderDetails od
        WHERE EXISTS (SELECT 1 FROM Production.Product p WHERE p.ProductID = od.ProductID);
        -- Add inventory check logic here if needed

        -- Update Order Status to Placed
        UPDATE Sales.SalesOrderHeader
        SET Status = 2 -- Status 2 = Placed
        WHERE OrderID = @NewOrderID;

        COMMIT TRANSACTION;
        SELECT @NewOrderID AS OrderID;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        THROW; -- Re-throw the error
    END CATCH
END
                    
                

Calculate Monthly Sales Summary

Generates a summary of sales for a given month and year.

                    
CREATE PROCEDURE usp_GetMonthlySalesSummary
    @Year INT,
    @Month INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        soh.OrderDate,
        c.FirstName + ' ' + c.LastName AS CustomerName,
        SUM(sod.LineTotal) AS TotalSalesAmount
    FROM Sales.SalesOrderHeader soh
    JOIN Sales.Customer c ON soh.CustomerID = c.CustomerID
    JOIN Sales.SalesOrderDetail sod ON soh.OrderID = sod.OrderID
    WHERE YEAR(soh.OrderDate) = @Year
      AND MONTH(soh.OrderDate) = @Month
    GROUP BY soh.OrderDate, c.FirstName, c.LastName
    ORDER BY soh.OrderDate;
END
                    
                

Performance Tuning & Advanced Techniques

Learn about optimizing stored procedures for better performance in Azure SQL Database, including indexing considerations and efficient query patterns.

Efficient Data Retrieval with Filtering

Demonstrates using appropriate indexes and filtering for fast data retrieval.

                    
-- Assumes an index on (CustomerID, OrderDate) for optimal performance
CREATE PROCEDURE usp_GetCustomerOrdersByDateRange
    @CustomerID INT,
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        OrderID,
        OrderDate,
        TotalDue
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID
      AND OrderDate BETWEEN @StartDate AND @EndDate
    ORDER BY OrderDate;
END
                    
                

Using Table Variables for Temporary Data

Illustrates the use of table variables for intermediate processing.

                    
CREATE PROCEDURE usp_ProcessBatchUpdates
    @ProductUpdates StructuredProductUpdateList READONLY -- User-defined table type
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @UpdatedProducts TABLE (ProductID INT);

    UPDATE Production.Product
    SET ListPrice = pu.NewPrice
    OUTPUT inserted.ProductID INTO @UpdatedProducts
    FROM Production.Product p
    JOIN @ProductUpdates pu ON p.ProductID = pu.ProductID;

    -- Further processing or logging based on @UpdatedProducts
    SELECT ProductID FROM @UpdatedProducts;
END
                    
                