Customer Order Summary Stored Procedure

This challenge focuses on creating a SQL stored procedure to efficiently retrieve summarized order information for a given customer. Stored procedures are valuable for encapsulating complex logic, improving performance, and enhancing security in database operations.

Problem Description

You need to develop a stored procedure that accepts a customer identifier as input and returns a summary of their orders. The summary should include the total number of orders placed by the customer and the total value of all their orders.

Key Requirements:

• The stored procedure should take one input parameter: CustomerID (an integer or a suitable identifier type for your database).
• The stored procedure should return two output values:
  • TotalOrders: The total count of orders placed by the customer.
  • TotalOrderValue: The sum of the monetary value of all orders placed by the customer.
• If a customer has no orders, TotalOrders should be 0 and TotalOrderValue should be 0.
• The procedure should handle cases where the CustomerID does not exist in the Customers table gracefully (e.g., return 0 for both output parameters).

Expected Behavior: When executed with a valid CustomerID, the procedure should query the Orders table, filter by the provided CustomerID, and calculate the aggregate values.

Examples

Example 1:

Assume the following Customers and Orders tables:

Customers table:

Orders table:

Input to Stored Procedure: CustomerID = 1

Output of Stored Procedure:

• TotalOrders = 2
• TotalOrderValue = 226.25

Explanation: Customer with CustomerID = 1 has placed 2 orders, and the sum of their TotalAmount is 150.75 + 75.50 = 226.25.

Example 2:

Input to Stored Procedure: CustomerID = 2

Output of Stored Procedure:

• TotalOrders = 1
• TotalOrderValue = 200.00

Explanation: Customer with CustomerID = 2 has placed 1 order with a TotalAmount of 200.00.

Example 3: (Edge Case)

Input to Stored Procedure: CustomerID = 3 (Customer does not exist)

Output of Stored Procedure:

• TotalOrders = 0
• TotalOrderValue = 0.00

Explanation: Customer with CustomerID = 3 is not found in the Customers table, or has no associated orders. Therefore, the counts are zero.

Constraints

• CustomerID will be a non-negative integer.
• TotalAmount in the Orders table will be a non-negative decimal or numeric type.
• The Orders table can contain up to 1,000,000 records.
• The Customers table can contain up to 100,000 records.
• The stored procedure should execute within 500 milliseconds for typical datasets.

Notes

• You will need to define the structure of the Customers and Orders tables (or assume a standard structure with at least the columns mentioned in the examples).
• Consider how your chosen SQL dialect handles output parameters or return values from stored procedures.
• Think about potential performance optimizations, especially if the Orders table is very large. Indexing relevant columns like CustomerID in the Orders table would be beneficial.
• The problem is language-agnostic in terms of SQL syntax, but you should use standard SQL constructs.