How to Retrieve Data With Stored Procedures

Advantages and Disadvantages of Various Methods

Suppose we have an order system comprising two tables: Order and OrderProduct.

CREATE TABLE [Order] (
    OrderID INT PRIMARY KEY,
    OrderDate DATETIME,
    CustomerCode NVARCHAR(50),
    CustomerName NVARCHAR(100)
);

CREATE TABLE OrderProduct (
    OrderProductID INT PRIMARY KEY,
    OrderID INT,
    ItemCode NVARCHAR(50),
    Description NVARCHAR(255),
    Quantity INT,
    Price DECIMAL(10, 2),

    FOREIGN KEY (OrderID) REFERENCES [Order](OrderID)
);

Inserting Sample Data

INSERT INTO [Order] (OrderID, OrderDate, CustomerCode, CustomerName) VALUES
(1, '2023-12-01', 'CUST100', 'Acme Corp'),
(2, '2023-12-02', 'CUST101', 'Globex Inc.'),
(3, '2023-12-03', 'CUST102', 'Initech');

-- Order 1 Products
INSERT INTO OrderProduct (OrderProductID, OrderID, ItemCode, Description, Quantity, Price) VALUES
(1, 1, 'ITM001', 'Wireless Mouse', 2, 19.99),
(2, 1, 'ITM002', 'Keyboard', 1, 49.99),
(3, 1, 'ITM003', 'Monitor Stand', 1, 35.50);

-- Order 2 Products
INSERT INTO OrderProduct (OrderProductID, OrderID, ItemCode, Description, Quantity, Price) VALUES
(4, 2, 'ITM004', 'USB Hub', 3, 15.00),
(5, 2, 'ITM005', 'HDMI Cable', 2, 10.00),
(6, 2, 'ITM006', 'Laptop Stand', 1, 29.99);

-- Order 3 Products
INSERT INTO OrderProduct (OrderProductID, OrderID, ItemCode, Description, Quantity, Price) VALUES
(7, 3, 'ITM007', 'Desk Lamp', 1, 22.50),
(8, 3, 'ITM008', 'Notebook', 5, 3.99),
(9, 3, 'ITM009', 'Pen Set', 1, 12.99);

We will develop three stored procedures for demonstration purposes to illustrate data returning and retrieving techniques:

1. GetOrderProductAmount: This procedure accepts an OrderID as an input parameter and returns the individual product amounts for the specified order.

2. GetOrderAmount: Taking an OrderID as its input, this procedure calculates the total amount of the order by leveraging the results from GetOrderProductAmount.

3. GetCustomerAmount: This procedure, provided with a CustomerCode, computes the aggregate amount of all orders placed by the customer, utilizing the output of GetOrderAmount.

It’s important to note that in practical applications, it’s not typically necessary to chain stored procedures in this manner for computations. These procedures are designed as examples to showcase the concepts of data return and retrieval in SQL.

Using result sets

Here’s the SQL script for the stored procedure GetOrderProductAmount:

CREATE PROCEDURE GetOrderProductAmount
    @OrderID INT
AS
BEGIN
    SELECT 
        OrderProductID, 
        ItemCode, 
        Description, 
        Quantity, 
        Price, 
        (Quantity * Price) AS Amount
    FROM 
        OrderProduct
    WHERE 
        OrderID = @OrderID
END

With GetOrderProductAmount, it can be invoked as follows:

EXEC GetOrderProductAmount 1

It will display the results.

Now, let’s write GetOrderAmount

CREATE PROCEDURE GetOrderAmount
    @OrderID INT
AS
BEGIN
    -- Declare a table variable to hold the product amounts
    DECLARE @ProductAmounts TABLE (
        OrderProductID INT,
        ItemCode NVARCHAR(50),
        Description NVARCHAR(255),
        Quantity INT,
        Price DECIMAL(10, 2),
        Amount DECIMAL(10, 2)
    );

    -- Insert the result set from GetOrderProductAmount into the table variable
    INSERT INTO @ProductAmounts (OrderProductID, ItemCode, Description, Quantity, Price, Amount)
        EXEC GetOrderProductAmount @OrderID;

    -- Calculate the total amount for the order
    SELECT 
        SUM(Amount) AS TotalOrderAmount
    FROM 
        @ProductAmounts;
END

As observed, SQL Server does not natively support the direct invocation of one stored procedure and the aggregation of its result set within another procedure. Instead, a temporary table or a table variable can be utilized to capture the result set from “GetOrderProductAmount,” followed by performing the aggregation.

Here is the version using a temporary table.

ALTER PROCEDURE GetOrderAmount
    @OrderID INT
AS
BEGIN
    -- Create a temporary table to store product amounts
    CREATE TABLE #ProductAmounts (
        OrderProductID INT,
        ItemCode NVARCHAR(50),
        Description NVARCHAR(255),
        Quantity INT,
        Price DECIMAL(10, 2),
        Amount DECIMAL(10, 2)
    );

    -- Insert the result set from GetOrderProductAmount into the temporary table
    INSERT INTO #ProductAmounts (OrderProductID, ItemCode, Description, Quantity, Price, Amount)
        EXEC GetOrderProductAmount @OrderID;

    -- Calculate the total amount for the order
    SELECT 
        SUM(Amount) AS TotalOrderAmount
    FROM 
        #ProductAmounts;

    -- Drop the temporary table
    DROP TABLE #ProductAmounts;
END

Now, we can invoke the stored procedure GetOrderAmount.

EXEC GetOrderAmount 1

It functions correctly.

Now, let’s write stored procedure GetCustomerAmount:

CREATE PROCEDURE GetCustomerAmount
    @CustomerCode NVARCHAR(50)
AS
BEGIN
    -- Create a temporary table to store order amounts
    CREATE TABLE #CustomerOrderAmounts (
        TotalOrderAmount DECIMAL(10, 2)
    );

    -- Temporarily store the order IDs for the customer
    DECLARE @OrderID INT;
    DECLARE OrderCursor CURSOR FOR 
        SELECT OrderID 
        FROM [Order] 
        WHERE CustomerCode = @CustomerCode;

    -- Open the cursor and fetch from it
    OPEN OrderCursor;
    FETCH NEXT FROM OrderCursor INTO @OrderID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- For each order, get the total amount and store in the temporary table
        INSERT INTO #CustomerOrderAmounts (TotalOrderAmount)
            EXEC GetOrderAmount @OrderID;

        FETCH NEXT FROM OrderCursor INTO @OrderID;
    END

    -- Close and deallocate the cursor
    CLOSE OrderCursor;
    DEALLOCATE OrderCursor;

    -- Calculate the total amount for the customer
    SELECT 
        @CustomerCode AS CustomerCode, 
        SUM(TotalOrderAmount) AS TotalCustomerAmount
    FROM 
        #CustomerOrderAmounts;

    -- Drop the temporary table
    DROP TABLE #CustomerOrderAmounts;
END

Now, we can execute the following SQL:

EXEC GetCustomerAmount 'CUST100'

Will it work? No. It will throw the following error:

Msg 8164, Level 16, State 1, Procedure GetOrderAmount, Line 16
An INSERT EXEC statement cannot be nested.

The error we’re encountering is due to the limitation in SQL Server where you cannot have a nested INSERT EXEC, i.e., you cannot execute a stored procedure that uses INSERT EXEC within another stored procedure that also uses INSERT EXEC.

Using a return code

Assuming we must use GetOrderAmount to implement GetCustomerAmount, and GetOrderProductAmount to implement GetOrderAmount, what steps should be taken?

Is it feasible for GetOrderAmount to return the value directly instead of utilizing result sets?

ALTER PROCEDURE GetOrderAmount
    @OrderID INT
AS
BEGIN
    -- Declare a table variable to hold the product amounts
    DECLARE @ProductAmounts TABLE (
        OrderProductID INT,
        ItemCode NVARCHAR(50),
        Description NVARCHAR(255),
        Quantity INT,
        Price DECIMAL(10, 2),
        Amount DECIMAL(10, 2)
    );

    -- Insert the result set from GetOrderProductAmount into the table variable
    INSERT INTO @ProductAmounts (OrderProductID, ItemCode, Description, Quantity, Price, Amount)
        EXEC GetOrderProductAmount @OrderID;

    -- Calculate the total amount for the order
    DECLARE @TotalOrderAmount DECIMAL(10, 2)
    SELECT 
        @TotalOrderAmount = SUM(Amount)
    FROM 
        @ProductAmounts;

    RETURN @TotalOrderAmount
END

Then, GetCustomerAmount can be modified as follows:

ALTER PROCEDURE GetCustomerAmount
    @CustomerCode NVARCHAR(50)
AS
BEGIN

    DECLARE @TotalCustomerAmount DECIMAL(10, 2)    
    DECLARE @TotalOrderAmount DECIMAL(10, 2)    

    SET @TotalCustomerAmount = 0;
    SET @TotalOrderAmount = 0;

    -- Temporarily store the order IDs for the customer
    DECLARE @OrderID INT;
    DECLARE OrderCursor CURSOR FOR 
        SELECT OrderID 
        FROM [Order] 
        WHERE CustomerCode = @CustomerCode;

    -- Open the cursor and fetch from it
    OPEN OrderCursor;
    FETCH NEXT FROM OrderCursor INTO @OrderID;
    WHILE @@FETCH_STATUS = 0
    BEGIN

        EXEC @TotalOrderAmount = GetOrderAmount @OrderID;
        SET @TotalCustomerAmount = @TotalCustomerAmount + @TotalOrderAmount
        FETCH NEXT FROM OrderCursor INTO @OrderID;
    END

    -- Close and deallocate the cursor
    CLOSE OrderCursor;
    DEALLOCATE OrderCursor;

    -- Calculate the total amount for the customer
    SELECT 
        @CustomerCode AS CustomerCode, 
        @TotalCustomerAmount AS TotalCustomerAmount    

END

Now, let’s proceed with execution:

EXEC GetCustomerAmount 'CUST100'

The resulting output:

It appears to be functioning, but the number is incorrect. The correct number should be 125.47. Why are the decimals missing?

In SQL Server, the RETURN statement can only return a integer!

The returned code’s purpose is to indicate a procedure’s execution status. While RETURN can be used without specifying a value, resulting in an implicit return value of 0 (commonly indicating success), it is typically employed to return an integer value. This integer usually represents a status code, where zero often signifies success, and non-zero values denote various types of errors or specific outcomes.

Although you can use the returned status code to relay some data, provided you only need to return an integer, it is still not advisable to do so.

To return a decimal value, such as the total order amount, it is more appropriate to use an OUTPUT parameter.

Using an output parameter

We can revise “GetOrderAmount” to utilize an output parameter.

ALTER PROCEDURE GetOrderAmount
    @OrderID INT,
    @TotalOrderAmount DECIMAL(10, 2) OUTPUT
AS
BEGIN
    -- Declare a table variable to hold the product amounts
    DECLARE @ProductAmounts TABLE (
        OrderProductID INT,
        ItemCode NVARCHAR(50),
        Description NVARCHAR(255),
        Quantity INT,
        Price DECIMAL(10, 2),
        Amount DECIMAL(10, 2)
    );

    -- Insert the result set from GetOrderProductAmount into the table variable
    INSERT INTO @ProductAmounts (OrderProductID, ItemCode, Description, Quantity, Price, Amount)
        EXEC GetOrderProductAmount @OrderID;

    -- Calculate the total amount for the order
    SELECT 
        @TotalOrderAmount = SUM(Amount)
    FROM 
        @ProductAmounts;
END

Then, we can rewrite GetCustomerAmount

ALTER PROCEDURE GetCustomerAmount
    @CustomerCode NVARCHAR(50)
AS
BEGIN
    -- Variable to hold the total amount for each order
    DECLARE @OrderTotal DECIMAL(10, 2);

    -- Variable to accumulate the total amount for the customer
    DECLARE @TotalCustomerAmount DECIMAL(10, 2) = 0.0;

    -- Cursor to iterate through the customer's orders
    DECLARE OrderCursor CURSOR FOR 
        SELECT OrderID 
        FROM [Order] 
        WHERE CustomerCode = @CustomerCode;

    -- Open the cursor
    OPEN OrderCursor;

    -- Fetch the next order
    DECLARE @OrderID INT;
    FETCH NEXT FROM OrderCursor INTO @OrderID;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Call GetOrderAmount for each order
        EXEC GetOrderAmount @OrderID, @OrderTotal OUTPUT;

        -- Add the order total to the customer's total
        SET @TotalCustomerAmount += @OrderTotal;

        -- Fetch the next order
        FETCH NEXT FROM OrderCursor INTO @OrderID;
    END

    -- Close and deallocate the cursor
    CLOSE OrderCursor;
    DEALLOCATE OrderCursor;

    -- Return the total amount spent by the customer
    SELECT @CustomerCode AS CustomerCode, @TotalCustomerAmount AS TotalCustomerAmount;
END

Now, we can try

EXEC GetCustomerAmount 'CUST100'

It works now.

Using cursor output parameter

What if I need to return multiple rows of data? In this situation, cursor output parameters are a useful option.

For instance, we can rewrite the stored procedure GetOrderProductAmount as follows:

ALTER PROCEDURE GetOrderProductAmount
    @OrderID INT,
    @ProductCursor CURSOR VARYING OUTPUT 
AS
BEGIN

    SET @ProductCursor = CURSOR FORWARD_ONLY STATIC FOR  
    SELECT 
        OrderProductID, 
        ItemCode, 
        Description, 
        Quantity, 
        Price, 
        (Quantity * Price) AS Amount
    FROM 
        OrderProduct
    WHERE 
        OrderID = @OrderID

    OPEN @ProductCursor;  
END

Then, we can access the result in GetOrderAmount as follows:

ALTER PROCEDURE GetOrderAmount
    @OrderID INT,
    @TotalOrderAmount DECIMAL(10, 2) OUTPUT
AS
BEGIN

    DECLARE @OrderProductID INT
    DECLARE @ItemCode NVARCHAR(50)
    DECLARE @Description NVARCHAR(255)
    DECLARE @Quantity INT
    DECLARE @Price DECIMAL(10, 2)
    DECLARE @Amount DECIMAL(10, 2)

    DECLARE @MyCursor CURSOR;  

    SET @TotalOrderAmount = 0
    EXEC GetOrderProductAmount @OrderID, @ProductCursor = @MyCursor OUTPUT;  

    FETCH NEXT FROM @MyCursor INTO @OrderProductID,@ItemCode,@Description,@Quantity,@Price,@Amount; 
    WHILE (@@FETCH_STATUS = 0)  
    BEGIN;  
        SET @TotalOrderAmount = @TotalOrderAmount + @Amount
        FETCH NEXT FROM @MyCursor INTO @OrderProductID,@ItemCode,@Description,@Quantity,@Price,@Amount; 
    END;  

    CLOSE @MyCursor;  
    DEALLOCATE @MyCursor;     

END

Finally, clean up

DROP PROCEDURE GetCustomerAmount;
DROP PROCEDURE GetOrderAmount
DROP PROCEDURE GetOrderProductAmount
DROP TABLE OrderProduct
DROP TABLE [Order]