SQL Server Call Stored Procedure from another Stored Procedure

In this SQL Server tutorial, you will learn about SQL Server Call Stored Procedure from another Stored Procedure.

As a database developer, you must know about the stored procedure and how to use it to enhance your database code’s modularity, maintainability, and reusability.

So, I will explain from scratch how to create a stored procedure and call one stored procedure from another.

Table of Contents

SQL Server Call Stored Procedure from another Stored Procedure

Calling one stored procedure from another stored procedure is referred to as Stored procedure composition.

This strategy allows a database developer to break tasks into smaller, more manageable components, promoting code reusability and modular design.

By including one stored procedure into another, you can abstract and organize logic effectively, which leads to a cleaner and more maintainable codebase and also enhances the code modularity.

Using one stored procedure within another makes operation very efficient and effective.

Here, you will understand how to create a table with data and create two stored procedures.

Let’s begin,

Creating Table for Stored Procedure

Create a table Orders with columns OrderID, OrderDate, TotalCost, and Processed using the query below.

CREATE TABLE Orders (
	OrderID INT PRIMARY KEY,
	OrderDate DATETIME,
	TotalCost DECIMAL(10, 2),
	Processed BIT
);

Insert the data into the Orders table.

INSERT INTO Orders (OrderID, OrderDate, TotalCost, Processed)
VALUES
	(1, '2024-02-20', NULL, 0),
	(2, '2024-02-21', NULL, 0),
	(3, '2024-02-22', NULL, 0),
	(4, '2024-02-23', NULL, 0),
	(5, '2024-02-24', NULL, 0),
	(6, '2024-02-25', NULL, 0),
	(7, '2024-02-26', NULL, 0),
	(8, '2024-02-27', NULL, 0),
	(9, '2024-02-28', NULL, 0),
	(10, '2024-02-29', NULL, 0);

View the Orders table using the query.

SELECT * FROM Orders;

Create a second table, OrdersDetails, with columns OrderDetailID, OrderID, ProductID, Quantity, and UnitPrice using the query below.

CREATE TABLE OrderDetails (
	OrderDetailID INT PRIMARY KEY,
	OrderID INT,
	ProductID INT,
	Quantity INT,
	UnitPrice DECIMAL(10, 2)
);

Insert some data into the OrderDetails table.

INSERT INTO OrderDetails (OrderDetailID, OrderID, ProductID, Quantity, UnitPrice)
VALUES
	(1, 1, 101, 2, 10.99),
	(2, 1, 102, 3, 15.99),
	(3, 2, 103, 1, 20.99),
	(4, 3, 104, 4, 8.99),
	(5, 4, 105, 2, 12.99),
	(6, 5, 106, 1, 18.99),
	(7, 6, 107, 3, 9.99),
	(8, 7, 108, 5, 14.99),
	(9, 8, 109, 2, 16.99),
	(10, 9, 110, 1, 22.99);

View the OrderDetails table using the below query.

SELECT * FROM OrderDetails;

After creating tables Orders and OrderDetails, use the below command to create a stored procedure CalculateOrderTotals to compute the total cost of an order based on the quantity and price of items ordered.

CREATE PROCEDURE CalculateOrderTotals
	@OrderID INT
AS
BEGIN
	DECLARE @TotalCost DECIMAL(10, 2);
    

	SELECT @TotalCost = SUM(Quantity * UnitPrice)
	FROM OrderDetails
	WHERE OrderID = @OrderID;
    
	UPDATE Orders SET TotalCost = @TotalCost WHERE OrderID = @OrderID;
END;

Here, the above CalculateOrderTotals stored procedure accepts the order ID and computes the total cost based on that order ID.

Within the stored procedure, the statement @TotalCost = SUM(Quantity * UnitPrice) computes the total cost by multiplying the Quantity and UnitPrice.

Then, in the next statement, UPDATE Orders SET TotalCost = @TotalCost WHERE OrderID = @OrderID. This statement updates the total cost based on the computed total cost for the specified OrderID.

Now consider a database application for managing customer orders. So here, I will show you how stored procedure composition can be used to automate order processing tasks efficiently.

Create another stored procedure. ProcessOrder is responsible for processing incoming orders. This procedure validates order details and calculates order totals.

CREATE PROCEDURE ProcessOrder
	@OrderID INT
AS
BEGIN
    
	-- Calculate order totals
	EXEC CalculateOrderTotals @OrderID;

	-- Mark order as processed
	UPDATE Orders SET Processed = 1 WHERE OrderID = @OrderID;
END;

Here, Creating stored procedure ProcessOrder, this stored procedure calls another stored procedure CalculateOrderTotals using statement EXEC CalculateOrderTotals @OrderID.

The next statement, UPDATE Orders SET Processed = 1 WHERE OrderID = @OrderID, marks the order as processed.

You can call multiple stored procedures in ProcessOrder, but this is just an example. In real life, you must include many stored procedures in a single stored procedure. Each procedure within another procedure performs a single operation.

For example, the CalculateOrderTotals will compute the order total within the Process stored procedure.

Now call the ProcessOrder procedure with orderID equal to 4 using the below query.

EXEC ProcessOrder 4;

Now, view the Orders table.

SELECT * FROM Orders;

SQL Server Call Stored Procedure from another Stored Procedure

As you can see, the order with ID 4 is processed, and the total cost is 25.98.

Here, when you execute the stored procedure ProcessOrder with an order equal to 4, then, within ProcessOrder, another stored procedure is called or executed with the same order ID, like ‘EXEC CalculateOrderTotals @OrderID’.

First, this CalcualteOrderTotals procedure computes the total cost of the provided order ID and then the next statement, ‘UPDATE Orders SET Processed = 1 WHERE OrderID = @OrderID;’ updates the same order column Processed with value 1.

So here, when you call the ProcessOrder stored procedure, it internally calls another stored procedure, CalcualteOrderTotals.

In the same way, you can call any number of stored procedures within another stored procedure. In real life, this CalcualteOrderTotals can also call another stored procedure for further processing or operation.

This is how the SQL Server Call Stored Procedure from another Stored Procedure.

Conclusion

In this SQL Server tutorial, you learned how the SQL Server Call Stored Procedure from another Stored Procedure.

You have created two tables, Orders and OrderDatials, then made two stored procedures, CalculateOrderTotals and ProcessOrder.

Then, you included the CalculateOrderTotals within the ProcessOrder procedure so that whenever ProcessOrder is called, the CalculateOrderTotals procedure will also be called. This is how you learn how to call a stored procedure from another stored procedure.