Inventory Management with Stored Procedures

This challenge focuses on designing and implementing stored procedures in SQL to manage an inventory system. Efficient inventory management is crucial for businesses to track stock levels, prevent shortages, and optimize ordering processes. You will create stored procedures to handle common inventory operations like adding new items, updating quantities, and retrieving inventory reports.

Problem Description

You are tasked with creating a set of stored procedures for a simple inventory management system. The system tracks items with the following attributes: item_id (INT, Primary Key, Auto-Increment), item_name (VARCHAR(255), Not Null), description (TEXT), and quantity (INT, Not Null, Default 0). The database table is named inventory.

You need to implement the following stored procedures:

1. AddInventoryItem(itemName VARCHAR(255), description TEXT): Adds a new item to the inventory table. The item_name is required, and the description is optional. The item_id will be automatically generated. The quantity defaults to 0.
2. UpdateInventoryQuantity(itemId INT, newQuantity INT): Updates the quantity of an existing item. The itemId must exist in the inventory table. The newQuantity must be a non-negative integer.
3. GetInventoryReport(): Returns a report showing all items in the inventory, including item_id, item_name, description, and quantity. The report should be sorted by item_name in ascending order.
4. GetLowStockItems(threshold INT): Returns a report of items with a quantity less than or equal to the provided threshold. The report should include item_id, item_name, description, and quantity. The report should be sorted by quantity in ascending order.

Expected Behavior:

• Stored procedures should handle invalid inputs gracefully (e.g., non-existent itemId, negative newQuantity). While explicit error handling (like RAISE) isn't strictly required for this challenge, the procedures should not cause the database to crash or produce incorrect results due to bad input.
• The UpdateInventoryQuantity procedure should update the quantity only if the provided itemId exists.
• The GetInventoryReport and GetLowStockItems procedures should return an empty result set if the table is empty.

Examples

Example 1:

Input: AddInventoryItem('Laptop', 'High-performance laptop for developers')
Output: (No direct output, but a new row is inserted into the inventory table with item_id = 1, item_name = 'Laptop', description = 'High-performance laptop for developers', quantity = 0)
Explanation: A new item is added to the inventory with the given name and description, and the quantity is set to the default value of 0.

Example 2:

Input: UpdateInventoryQuantity(1, 10)
Output: (No direct output, but the quantity for item_id 1 is updated to 10)
Explanation: The quantity of the item with item_id 1 is updated to 10.

Example 3:

Input: GetLowStockItems(5)
Output:
item_id | item_name | description | quantity
------- | ---------- | ----------- | --------
1       | Laptop     | ...         | 0
2       | Mouse      | ...         | 2
Explanation:  This assumes item_id 1 (Laptop) has a quantity of 0 and item_id 2 (Mouse) has a quantity of 2.  All other items have a quantity greater than 5. The result set contains only items with a quantity less than or equal to 5, sorted by quantity.

Constraints

• item_name cannot be longer than 255 characters.
• newQuantity must be a non-negative integer (0 or greater).
• threshold in GetLowStockItems must be a non-negative integer.
• The database system is assumed to be a standard SQL implementation (e.g., MySQL, PostgreSQL, SQL Server). The specific syntax might need minor adjustments depending on the chosen database.
• Performance is not a primary concern for this challenge, but avoid excessively inefficient queries.

Notes

• Consider using parameterized queries to prevent SQL injection vulnerabilities.

• Think about how to handle potential errors, such as attempting to update a non-existent item. While explicit error handling isn't mandatory, the procedures should not fail catastrophically.

• Pseudocode for the stored procedures:

  AddInventoryItem(itemName VARCHAR(255), description TEXT)

  BEGIN
    INSERT INTO inventory (item_name, description)
    VALUES (itemName, description);
  END
  

  UpdateInventoryQuantity(itemId INT, newQuantity INT)

  BEGIN
    UPDATE inventory
    SET quantity = newQuantity
    WHERE item_id = itemId;
  END
  

  GetInventoryReport()

  BEGIN
    SELECT item_id, item_name, description, quantity
    FROM inventory
    ORDER BY item_name ASC;
  END
  

  GetLowStockItems(threshold INT)

  BEGIN
    SELECT item_id, item_name, description, quantity
    FROM inventory
    WHERE quantity <= threshold
    ORDER BY quantity ASC;
  END