Write a Procedure to Log Inventory Change
Write a Procedure to Log Inventory Change
InventoryLog Table:
QueryExplanation:
-
InventoryLog Table:
Stores details like product ID, old quantity, new quantity, and timestamp of change. -
Stored Procedure
LogInventoryChange:-
Takes 3 inputs:
@ProductID,@OldQuantity, and@NewQuantity. -
Inserts a new record into the
InventoryLogtable each time it's executed.
-
-
Procedure Execution:
-
The
EXECline simulates a change in inventory for product ID101, from quantity50to30.
-
SQL Query:
-- Step 1: Create the InventoryLog table (if not already exists)
-- This table will store the inventory change logs
CREATE TABLE InventoryLog (
LogID INT IDENTITY(1,1) PRIMARY KEY,
ProductID INT,
OldQuantity INT,
NewQuantity INT,
ChangedOn DATETIME DEFAULT GETDATE()
);
GO
-- Step 2: Create the stored procedure to log inventory change
CREATE PROCEDURE dbo.LogInventoryChange
@ProductID INT,
@OldQuantity INT,
@NewQuantity INT
AS
BEGIN
-- Insert a record into the log table capturing inventory changes
INSERT INTO InventoryLog (ProductID, OldQuantity, NewQuantity)
VALUES (@ProductID, @OldQuantity, @NewQuantity);
END;
GO
-- Step 3: Execute the procedure to log an inventory change
EXEC dbo.LogInventoryChange @ProductID = 101, @OldQuantity = 50, @NewQuantity = 30;
Output:
