Building a Scalable Inventory Management System Using SQL and Stored Procedures

Managing inventory effectively is essential for many businesses, especially as they grow. Using SQL along with stored procedures can help you build a scalable inventory system that is easy to maintain, efficient, and reduces errors. In this tutorial, we will cover the basics of setting up tables and creating stored procedures to handle common inventory operations like adding stock, reducing stock, and viewing current inventory.

First, let's create the basic table structure to keep track of products and their quantities.

CREATE TABLE Products (
  ProductID INT PRIMARY KEY IDENTITY(1,1),
  ProductName VARCHAR(100) NOT NULL,
  Quantity INT NOT NULL DEFAULT 0
);

With this table, each product has a unique ID, a name, and the current quantity in stock. Now, we will create a stored procedure to add new products into the inventory.

CREATE PROCEDURE AddProduct
  @ProductName VARCHAR(100),
  @InitialQuantity INT
AS
BEGIN
  INSERT INTO Products (ProductName, Quantity)
  VALUES (@ProductName, @InitialQuantity);
END;

Next, let's create a procedure to increase the quantity of an existing product when new stock arrives.

CREATE PROCEDURE IncreaseStock
  @ProductID INT,
  @Amount INT
AS
BEGIN
  UPDATE Products
  SET Quantity = Quantity + @Amount
  WHERE ProductID = @ProductID;
END;

We also need a stored procedure to reduce stock, for example when items are sold or removed from inventory. It's important to check that enough stock exists before subtracting.

CREATE PROCEDURE DecreaseStock
  @ProductID INT,
  @Amount INT
AS
BEGIN
  IF EXISTS (SELECT 1 FROM Products WHERE ProductID = @ProductID AND Quantity >= @Amount)
  BEGIN
    UPDATE Products
    SET Quantity = Quantity - @Amount
    WHERE ProductID = @ProductID;
  END
  ELSE
  BEGIN
    RAISERROR('Not enough stock to complete this operation.', 16, 1);
  END
END;

Finally, you might want to see a current snapshot of all products and their stock levels.

CREATE PROCEDURE GetInventory
AS
BEGIN
  SELECT ProductID, ProductName, Quantity FROM Products;
END;

With these stored procedures, your inventory management system supports scalable operations with clear, reusable code blocks. You can expand it by adding features such as transaction logging, user access control, or automated alerts when stock runs low.

To use a stored procedure, simply call it with the appropriate parameters. For example, to add 100 units of a new product "USB Cable":

EXEC AddProduct @ProductName = 'USB Cable', @InitialQuantity = 100;

This beginner-friendly approach allows you to leverage SQL's power and build a maintainable system that grows with your business needs.