-- Create Database

CREATE DATABASE ECommerceDB;

GO

USE ECommerceDB;

GO

-- 1. Customers Table

CREATE TABLE Customers (

    customer_id INT PRIMARY KEY IDENTITY(1,1),

    username VARCHAR(50) UNIQUE NOT NULL,

    email VARCHAR(100) UNIQUE NOT NULL CONSTRAINT CK_Customer_Email CHECK (email LIKE '%@%.%'),

    password_hash VARCHAR(255) NOT NULL,

    first_name VARCHAR(50) NOT NULL,

    last_name VARCHAR(50) NOT NULL,

    phone VARCHAR(15),

    registration_date DATE DEFAULT GETDATE(),

    last_login DATETIME DEFAULT GETDATE(),

    is_active BIT DEFAULT 1,

    balance DECIMAL(10,2) DEFAULT 0.00,

    profile_pic VARBINARY(MAX),

    preferred_language VARCHAR(20) CONSTRAINT CK_Preferred_Language CHECK (preferred_language IN ('English', 'Spanish', 'French')) DEFAULT 'English'

);

GO

-- 2. Sellers Table

CREATE TABLE Sellers (

    seller_id INT PRIMARY KEY IDENTITY(1,1),

    business_name VARCHAR(100) NOT NULL,

    email VARCHAR(100) UNIQUE NOT NULL CONSTRAINT CK_Seller_Email CHECK (email LIKE '%@%.%'),

    password_hash VARCHAR(255) NOT NULL,

    address VARCHAR(255),

    city VARCHAR(50),

    state VARCHAR(50),

    zip_code VARCHAR(10),

    country VARCHAR(50),

    phone VARCHAR(15),

    registration_date DATE DEFAULT GETDATE(),

    is_verified BIT DEFAULT 0,

    rating FLOAT DEFAULT 0.0,

    bank_account VARCHAR(50) UNIQUE

);

GO

-- 3. Categories Table (created before Products due to FK)

CREATE TABLE Categories (

    category_id INT PRIMARY KEY IDENTITY(1,1),

    name VARCHAR(50) UNIQUE NOT NULL,

    parent_id INT,

    description TEXT,

    icon_url VARCHAR(255),

    created_at DATETIME DEFAULT GETDATE(),

    updated_at DATETIME DEFAULT GETDATE(),

    is_active BIT DEFAULT 1,

    product_count INT DEFAULT 0,

    seo_keywords VARCHAR(255),

    min_price DECIMAL(10,2) DEFAULT 0.00,

    max_price DECIMAL(10,2) DEFAULT 999999.99,

    CONSTRAINT FK_Categories_Parent FOREIGN KEY (parent_id) REFERENCES Categories(category_id)

);

GO

-- 4. Products Table

CREATE TABLE Products (

    product_id INT PRIMARY KEY IDENTITY(1,1),

    name VARCHAR(100) NOT NULL,

    description TEXT,

    price DECIMAL(10,2) NOT NULL CONSTRAINT CK_Price CHECK (price > 0),

    stock INT DEFAULT 0 CONSTRAINT CK_Stock CHECK (stock >= 0),

    category_id INT,

    seller_id INT,

    created_at DATETIME DEFAULT GETDATE(),

    updated_at DATETIME DEFAULT GETDATE(),

    image_url VARCHAR(255),

    weight FLOAT,

    dimensions VARCHAR(50),

    is_available BIT DEFAULT 1,

    rating FLOAT DEFAULT 0.0,

    CONSTRAINT FK_Products_Category FOREIGN KEY (category_id) REFERENCES Categories(category_id),

    CONSTRAINT FK_Products_Seller FOREIGN KEY (seller_id) REFERENCES Sellers(seller_id) ON DELETE CASCADE

);

GO

-- Trigger for Products updated_at

CREATE TRIGGER TR_Products_Update ON Products AFTER UPDATE AS

UPDATE Products SET updated_at = GETDATE() FROM Products p INNER JOIN inserted i ON p.product_id = i.product_id;

GO

-- 5. Orders Table

CREATE TABLE Orders (

    order_id INT PRIMARY KEY IDENTITY(1,1),

    customer_id INT,

    order_date DATETIME DEFAULT GETDATE(),

    total_amount DECIMAL(10,2) NOT NULL,

    status VARCHAR(20) CONSTRAINT CK_Status CHECK (status IN ('Pending', 'Shipped', 'Delivered', 'Cancelled')) DEFAULT 'Pending',

    payment_id INT,

    shipping_address VARCHAR(255) NOT NULL,

    billing_address VARCHAR(255) NOT NULL,

    discount_applied DECIMAL(10,2) DEFAULT 0.00,

    tax_amount DECIMAL(10,2) DEFAULT 0.00,

    notes TEXT,

    tracking_number VARCHAR(50) UNIQUE,

    CONSTRAINT FK_Orders_Customer FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE

);

GO

-- 6. Order_Items Table

CREATE TABLE Order_Items (

    item_id INT PRIMARY KEY IDENTITY(1,1),

    order_id INT,

    product_id INT,

    quantity INT NOT NULL CONSTRAINT CK_Quantity CHECK (quantity > 0),

    price_per_unit DECIMAL(10,2) NOT NULL,

    subtotal DECIMAL(10,2) NOT NULL,

    discount DECIMAL(10,2) DEFAULT 0.00,

    added_at DATETIME DEFAULT GETDATE(),

    is_gift BIT DEFAULT 0,

    gift_message TEXT,

    return_status VARCHAR(20) CONSTRAINT CK_Return_Status CHECK (return_status IN ('None', 'Requested', 'Approved')) DEFAULT 'None',

    CONSTRAINT FK_OrderItems_Order FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE,

    CONSTRAINT FK_OrderItems_Product FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE

);

GO

-- 7. Payments Table

CREATE TABLE Payments (

    payment_id INT PRIMARY KEY IDENTITY(1,1),

    order_id INT,

    amount DECIMAL(10,2) NOT NULL,

    payment_date DATETIME DEFAULT GETDATE(),

    method VARCHAR(20) NOT NULL CONSTRAINT CK_Method CHECK (method IN ('Credit Card', 'PayPal', 'Bank Transfer')),

    status VARCHAR(20) CONSTRAINT CK_Payment_Status CHECK (status IN ('Success', 'Failed', 'Pending')) DEFAULT 'Pending',

    transaction_id VARCHAR(100) UNIQUE NOT NULL,

    card_last_four VARCHAR(4),

    expiry_date DATE,

    cvv VARCHAR(3) NOT NULL CONSTRAINT CK_CVV CHECK (LEN(cvv) = 3),

    billing_email VARCHAR(100) CONSTRAINT CK_Billing_Email CHECK (billing_email LIKE '%@%.%'),

    CONSTRAINT FK_Payments_Order FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE

);

GO

-- 8. Shipments Table

CREATE TABLE Shipments (

    shipment_id INT PRIMARY KEY IDENTITY(1,1),

    order_id INT,

    carrier VARCHAR(50) NOT NULL,

    tracking_number VARCHAR(50) UNIQUE NOT NULL,

    shipped_date DATE,

    delivery_date DATE,

    status VARCHAR(20) CONSTRAINT CK_Shipment_Status CHECK (status IN ('In Transit', 'Delivered', 'Delayed')) DEFAULT 'In Transit',

    cost DECIMAL(10,2) DEFAULT 0.00,

    weight FLOAT,

    dimensions VARCHAR(50),

    insurance_amount DECIMAL(10,2) DEFAULT 0.00,

    notes TEXT,

    CONSTRAINT FK_Shipments_Order FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE

);

GO

-- 9. Reviews Table

CREATE TABLE Reviews (

    review_id INT PRIMARY KEY IDENTITY(1,1),

    product_id INT,

    customer_id INT,

    rating INT NOT NULL CONSTRAINT CK_Rating CHECK (rating BETWEEN 1 AND 5),

    comment TEXT,

    review_date DATETIME DEFAULT GETDATE(),

    is_verified BIT DEFAULT 0,

    helpful_votes INT DEFAULT 0,

    images VARBINARY(MAX),

    video_url VARCHAR(255),

    response TEXT,

    updated_at DATETIME DEFAULT GETDATE(),

    CONSTRAINT FK_Reviews_Product FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE CASCADE,

    CONSTRAINT FK_Reviews_Customer FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)

);

GO

-- Trigger for Reviews updated_at

CREATE TRIGGER TR_Reviews_Update ON Reviews AFTER UPDATE AS

UPDATE Reviews SET updated_at = GETDATE() FROM Reviews r INNER JOIN inserted i ON r.review_id = i.review_id;

GO

-- 10. Addresses Table

CREATE TABLE Addresses (

    address_id INT PRIMARY KEY IDENTITY(1,1),

    customer_id INT,

    type VARCHAR(20) NOT NULL CONSTRAINT CK_Address_Type CHECK (type IN ('Shipping', 'Billing')),

    street VARCHAR(255) NOT NULL,

    city VARCHAR(50) NOT NULL,

    state VARCHAR(50) NOT NULL,

    zip_code VARCHAR(10) NOT NULL,

    country VARCHAR(50) NOT NULL,

    is_default BIT DEFAULT 0,

    phone VARCHAR(15),

    notes TEXT,

    created_at DATETIME DEFAULT GETDATE(),

    latitude FLOAT,

    longitude FLOAT,

    CONSTRAINT FK_Addresses_Customer FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE

);

GO

-- 11. Coupons Table

CREATE TABLE Coupons (

    coupon_id INT PRIMARY KEY IDENTITY(1,1),

    code VARCHAR(20) UNIQUE NOT NULL,

    discount_percentage DECIMAL(5,2) NOT NULL CONSTRAINT CK_Discount CHECK (discount_percentage BETWEEN 0 AND 100),

    min_order_amount DECIMAL(10,2) DEFAULT 0.00,

    max_discount DECIMAL(10,2),

    start_date DATE NOT NULL,

    end_date DATE NOT NULL,

    usage_limit INT DEFAULT 1 CONSTRAINT CK_Usage_Limit CHECK (usage_limit > 0),

    used_count INT DEFAULT 0,

    is_active BIT DEFAULT 1,

    applicable_category_id INT,

    description TEXT,

    CONSTRAINT FK_Coupons_Category FOREIGN KEY (applicable_category_id) REFERENCES Categories(category_id)

);

GO

-- Insert Sample Data (20 rows per table, varied for realism)

-- Customers (20 rows)

INSERT INTO Customers (username, email, password_hash, first_name, last_name, phone) VALUES 

('john_doe', 'john@example.com', 'hash1', 'John', 'Doe', '123-456-7890'),

('jane_smith', 'jane@example.com', 'hash2', 'Jane', 'Smith', '987-654-3210'),

('alice_johnson', 'alice@example.com', 'hash3', 'Alice', 'Johnson', '555-1234'),

('bob_brown', 'bob@example.com', 'hash4', 'Bob', 'Brown', '444-5678'),

('charlie_davis', 'charlie@example.com', 'hash5', 'Charlie', 'Davis', '333-9012'),

('diana_evans', 'diana@example.com', 'hash6', 'Diana', 'Evans', '222-3456'),

('edward_frank', 'edward@example.com', 'hash7', 'Edward', 'Frank', '111-7890'),

('fiona_green', 'fiona@example.com', 'hash8', 'Fiona', 'Green', '666-4321'),

('george_harris', 'george@example.com', 'hash9', 'George', 'Harris', '777-8765'),

('helen_iris', 'helen@example.com', 'hash10', 'Helen', 'Iris', '888-2109'),

('ian_jack', 'ian@example.com', 'hash11', 'Ian', 'Jack', '999-5432'),

('julia_king', 'julia@example.com', 'hash12', 'Julia', 'King', '000-9876'),

('kevin_lee', 'kevin@example.com', 'hash13', 'Kevin', 'Lee', '111-2222'),

('laura_martin', 'laura@example.com', 'hash14', 'Laura', 'Martin', '333-4444'),

('mike_nelson', 'mike@example.com', 'hash15', 'Mike', 'Nelson', '555-6666'),

('nina_oliver', 'nina@example.com', 'hash16', 'Nina', 'Oliver', '777-8888'),

('oscar_perez', 'oscar@example.com', 'hash17', 'Oscar', 'Perez', '999-0000'),

('paula_quinn', 'paula@example.com', 'hash18', 'Paula', 'Quinn', '222-3333'),

('quincy_ross', 'quincy@example.com', 'hash19', 'Quincy', 'Ross', '444-5555'),

('rachel_scott', 'rachel@example.com', 'hash20', 'Rachel', 'Scott', '666-7777');

GO

-- Sellers (20 rows)

INSERT INTO Sellers (business_name, email, password_hash, address, city, state, zip_code, country, phone) VALUES 

('TechStore', 'tech@store.com', 'hash3', '123 Main St', 'New York', 'NY', '10001', 'USA', '555-1234'),

('FashionHub', 'fashion@hub.com', 'hash13', '456 Elm St', 'Los Angeles', 'CA', '90001', 'USA', '555-5678'),

('BookWorld', 'book@world.com', 'hash14', '789 Oak St', 'Chicago', 'IL', '60601', 'USA', '555-9012'),

('GadgetPro', 'gadget@pro.com', 'hash15', '101 Pine St', 'Houston', 'TX', '77001', 'USA', '555-3456'),

('HomeEssentials', 'home@ess.com', 'hash16', '202 Maple St', 'Phoenix', 'AZ', '85001', 'USA', '555-7890'),

('SportsGear', 'sports@gear.com', 'hash17', '303 Birch St', 'Philadelphia', 'PA', '19101', 'USA', '555-4321'),

('BeautyShop', 'beauty@shop.com', 'hash18', '404 Cedar St', 'San Antonio', 'TX', '78201', 'USA', '555-8765'),

('ToyLand', 'toy@land.com', 'hash19', '505 Walnut St', 'San Diego', 'CA', '92101', 'USA', '555-2109'),

('AutoParts', 'auto@parts.com', 'hash20', '606 Spruce St', 'Dallas', 'TX', '75201', 'USA', '555-5432'),

('ElectronicsPlus', 'elec@plus.com', 'hash21', '707 Fir St', 'San Jose', 'CA', '95101', 'USA', '555-9876'),

('FurnitureDepot', 'furn@depot.com', 'hash22', '808 Ash St', 'Austin', 'TX', '78701', 'USA', '555-6543'),

('HealthStore', 'health@store.com', 'hash23', '909 Beech St', 'Jacksonville', 'FL', '32201', 'USA', '555-3210'),

('GroceryMart', 'grocery@mart.com', 'hash24', '1010 Chestnut St', 'San Francisco', 'CA', '94101', 'USA', '555-0987'),

('OfficeSupply', 'office@supply.com', 'hash25', '1111 Dogwood St', 'Indianapolis', 'IN', '46201', 'USA', '555-7654'),

('PetWorld', 'pet@world.com', 'hash26', '1212 Elmwood St', 'Columbus', 'OH', '43201', 'USA', '555-4320'),

('MusicStore', 'music@store.com', 'hash27', '1313 Firwood St', 'Charlotte', 'NC', '28201', 'USA', '555-1098'),

('ArtGallery', 'art@gallery.com', 'hash28', '1414 Green St', 'Fort Worth', 'TX', '76101', 'USA', '555-8760'),

('TravelGear', 'travel@gear.com', 'hash29', '1515 Holly St', 'Seattle', 'WA', '98101', 'USA', '555-5430'),

('JewelryBox', 'jewelry@box.com', 'hash30', '1616 Ivy St', 'Denver', 'CO', '80201', 'USA', '555-2100'),

('BikeShop', 'bike@shop.com', 'hash31', '1717 Juniper St', 'Washington', 'DC', '20001', 'USA', '555-9870');

GO

-- Categories (20 rows)

INSERT INTO Categories (name, description) VALUES 

('Electronics', 'Gadgets and devices'),

('Fashion', 'Clothing and accessories'),

('Books', 'Literature and education'),

('Home & Kitchen', 'Household items'),

('Sports', 'Fitness and outdoors'),

('Beauty', 'Cosmetics and care'),

('Toys', 'Games and fun'),

('Automotive', 'Car parts and tools'),

('Furniture', 'Home decor'),

('Health', 'Wellness products'),

('Groceries', 'Food and beverages'),

('Office Supplies', 'Stationery and equipment'),

('Pets', 'Animal care'),

('Music', 'Instruments and audio'),

('Art', 'Supplies and decor'),

('Travel', 'Luggage and gear'),

('Jewelry', 'Accessories and gems'),

('Bikes', 'Cycles and parts'),

('Gardening', 'Tools and plants'),

('Electronics Accessories', 'Cables and cases');

GO

-- Products (20 rows, referencing categories 1-20, sellers 1-20)

INSERT INTO Products (name, description, price, stock, category_id, seller_id, weight, dimensions) VALUES 

('Laptop', 'High-end gaming laptop', 1200.00, 50, 1, 1, 2.5, '15x10x1'),

('T-Shirt', 'Cotton casual wear', 20.00, 100, 2, 2, 0.2, 'M size'),

('Novel Book', 'Bestseller fiction', 15.00, 200, 3, 3, 0.5, '8x5x1'),

('Blender', 'Kitchen appliance', 50.00, 30, 4, 4, 3.0, '10x6x6'),

('Yoga Mat', 'Fitness mat', 25.00, 80, 5, 5, 1.0, '72x24x0.5'),

('Lipstick', 'Makeup item', 10.00, 150, 6, 6, 0.1, '3x1x1'),

('Action Figure', 'Toy for kids', 15.00, 120, 7, 7, 0.3, '6x4x2'),

('Car Tire', 'Automotive part', 100.00, 40, 8, 8, 10.0, '24x24x8'),

('Sofa', 'Living room furniture', 500.00, 10, 9, 9, 50.0, '80x40x30'),

('Vitamin Supplements', 'Health product', 30.00, 90, 10, 10, 0.4, '4x2x2'),

('Cereal', 'Breakfast food', 5.00, 300, 11, 11, 1.0, '12x8x4'),

('Notebook', 'Office stationery', 3.00, 500, 12, 12, 0.2, '8x6x0.5'),

('Dog Food', 'Pet nutrition', 20.00, 100, 13, 13, 5.0, '20x15x5'),

('Guitar', 'Musical instrument', 200.00, 20, 14, 14, 4.0, '40x15x5'),

('Paint Set', 'Art supplies', 25.00, 50, 15, 15, 1.5, '12x8x2'),

('Suitcase', 'Travel luggage', 80.00, 30, 16, 16, 3.0, '28x20x10'),

('Necklace', 'Jewelry item', 50.00, 60, 17, 17, 0.1, '5x3x1'),

('Mountain Bike', 'Outdoor bike', 400.00, 15, 18, 18, 15.0, '60x30x20'),

('Garden Tools', 'Gardening kit', 35.00, 40, 19, 19, 2.0, '18x12x6'),

('Phone Case', 'Accessory', 10.00, 200, 20, 20, 0.1, '6x3x0.5');

GO

-- Orders (20 rows, referencing customers 1-20)

INSERT INTO Orders (customer_id, total_amount, shipping_address, billing_address, status, order_date) VALUES 

(1, 1200.00, '123 Home St', '123 Home St', 'Delivered', '2025-08-01'),

(2, 20.00, '456 Apt Rd', '456 Apt Rd', 'Shipped', '2025-08-02'),

(3, 15.00, '789 Villa Ln', '789 Villa Ln', 'Pending', '2025-08-03'),

(4, 50.00, '101 Condo Blvd', '101 Condo Blvd', 'Cancelled', '2025-08-04'),

(5, 25.00, '202 House Ave', '202 House Ave', 'Delivered', '2025-08-05'),

(6, 10.00, '303 Flat Dr', '303 Flat Dr', 'Shipped', '2025-08-06'),

(7, 15.00, '404 Bungalow Way', '404 Bungalow Way', 'Pending', '2025-08-07'),

(8, 100.00, '505 Mansion Pl', '505 Mansion Pl', 'Cancelled', '2025-08-08'),

(9, 500.00, '606 Cottage Ct', '606 Cottage Ct', 'Delivered', '2025-08-09'),

(10, 30.00, '707 Estate Terr', '707 Estate Terr', 'Shipped', '2025-08-10'),

(11, 5.00, '808 Ranch Rd', '808 Ranch Rd', 'Pending', '2025-08-11'),

(12, 3.00, '909 Farm Ln', '909 Farm Ln', 'Cancelled', '2025-08-12'),

(13, 20.00, '1010 City St', '1010 City St', 'Delivered', '2025-08-13'),

(14, 200.00, '1111 Town Rd', '1111 Town Rd', 'Shipped', '2025-08-14'),

(15, 25.00, '1212 Village Ln', '1212 Village Ln', 'Pending', '2025-08-15'),

(16, 80.00, '1313 Suburb Blvd', '1313 Suburb Blvd', 'Cancelled', '2025-08-16'),

(17, 50.00, '1414 Rural Ave', '1414 Rural Ave', 'Delivered', '2025-08-17'),

(18, 400.00, '1515 Urban Dr', '1515 Urban Dr', 'Shipped', '2025-08-18'),

(19, 35.00, '1616 Metro Way', '1616 Metro Way', 'Pending', '2025-08-19'),

(20, 10.00, '1717 Capital Pl', '1717 Capital Pl', 'Cancelled', '2025-08-20');

GO

-- Order_Items (20 rows, referencing orders 1-20, products 1-20)

INSERT INTO Order_Items (order_id, product_id, quantity, price_per_unit, subtotal) VALUES 

(1, 1, 1, 1200.00, 1200.00),

(2, 2, 1, 20.00, 20.00),

(3, 3, 1, 15.00, 15.00),

(4, 4, 1, 50.00, 50.00),

(5, 5, 1, 25.00, 25.00),

(6, 6, 1, 10.00, 10.00),

(7, 7, 1, 15.00, 15.00),

(8, 8, 1, 100.00, 100.00),

(9, 9, 1, 500.00, 500.00),

(10, 10, 1, 30.00, 30.00),

(11, 11, 1, 5.00, 5.00),

(12, 12, 1, 3.00, 3.00),

(13, 13, 1, 20.00, 20.00),

(14, 14, 1, 200.00, 200.00),

(15, 15, 1, 25.00, 25.00),

(16, 16, 1, 80.00, 80.00),

(17, 17, 1, 50.00, 50.00),

(18, 18, 1, 400.00, 400.00),

(19, 19, 1, 35.00, 35.00),

(20, 20, 1, 10.00, 10.00);

GO

-- Payments (20 rows, referencing orders 1-20)

INSERT INTO Payments (order_id, amount, method, transaction_id, cvv, billing_email, status) VALUES 

(1, 1200.00, 'Credit Card', 'TX1', '123', 'john@example.com', 'Success'),

(2, 20.00, 'PayPal', 'TX2', '456', 'jane@example.com', 'Success'),

(3, 15.00, 'Bank Transfer', 'TX3', '789', 'alice@example.com', 'Pending'),

(4, 50.00, 'Credit Card', 'TX4', '012', 'bob@example.com', 'Failed'),

(5, 25.00, 'PayPal', 'TX5', '345', 'charlie@example.com', 'Success'),

(6, 10.00, 'Bank Transfer', 'TX6', '678', 'diana@example.com', 'Success'),

(7, 15.00, 'Credit Card', 'TX7', '901', 'edward@example.com', 'Pending'),

(8, 100.00, 'PayPal', 'TX8', '234', 'fiona@example.com', 'Failed'),

(9, 500.00, 'Bank Transfer', 'TX9', '567', 'george@example.com', 'Success'),

(10, 30.00, 'Credit Card', 'TX10', '890', 'helen@example.com', 'Success'),

(11, 5.00, 'PayPal', 'TX11', '123', 'ian@example.com', 'Pending'),

(12, 3.00, 'Bank Transfer', 'TX12', '456', 'julia@example.com', 'Failed'),

(13, 20.00, 'Credit Card', 'TX13', '789', 'kevin@example.com', 'Success'),

(14, 200.00, 'PayPal', 'TX14', '012', 'laura@example.com', 'Success'),

(15, 25.00, 'Bank Transfer', 'TX15', '345', 'mike@example.com', 'Pending'),

(16, 80.00, 'Credit Card', 'TX16', '678', 'nina@example.com', 'Failed'),

(17, 50.00, 'PayPal', 'TX17', '901', 'oscar@example.com', 'Success'),

(18, 400.00, 'Bank Transfer', 'TX18', '234', 'paula@example.com', 'Success'),

(19, 35.00, 'Credit Card', 'TX19', '567', 'quincy@example.com', 'Pending'),

(20, 10.00, 'PayPal', 'TX20', '890', 'rachel@example.com', 'Failed');

GO

-- Shipments (20 rows, referencing orders 1-20)

INSERT INTO Shipments (order_id, carrier, tracking_number, cost, status, shipped_date, delivery_date) VALUES 

(1, 'UPS', 'TRACK1', 20.00, 'Delivered', '2025-08-02', '2025-08-05'),

(2, 'FedEx', 'TRACK2', 5.00, 'In Transit', '2025-08-03', NULL),

(3, 'USPS', 'TRACK3', 3.00, 'Delayed', '2025-08-04', NULL),

(4, 'DHL', 'TRACK4', 10.00, 'Delivered', '2025-08-05', '2025-08-08'),

(5, 'UPS', 'TRACK5', 7.00, 'In Transit', '2025-08-06', NULL),

(6, 'FedEx', 'TRACK6', 4.00, 'Delayed', '2025-08-07', NULL),

(7, 'USPS', 'TRACK7', 2.00, 'Delivered', '2025-08-08', '2025-08-11'),

(8, 'DHL', 'TRACK8', 15.00, 'In Transit', '2025-08-09', NULL),

(9, 'UPS', 'TRACK9', 50.00, 'Delayed', '2025-08-10', NULL),

(10, 'FedEx', 'TRACK10', 8.00, 'Delivered', '2025-08-11', '2025-08-14'),

(11, 'USPS', 'TRACK11', 1.00, 'In Transit', '2025-08-12', NULL),

(12, 'DHL', 'TRACK12', 2.00, 'Delayed', '2025-08-13', NULL),

(13, 'UPS', 'TRACK13', 4.00, 'Delivered', '2025-08-14', '2025-08-17'),

(14, 'FedEx', 'TRACK14', 25.00, 'In Transit', '2025-08-15', NULL),

(15, 'USPS', 'TRACK15', 6.00, 'Delayed', '2025-08-16', NULL),

(16, 'DHL', 'TRACK16', 12.00, 'Delivered', '2025-08-17', '2025-08-20'),

(17, 'UPS', 'TRACK17', 9.00, 'In Transit', '2025-08-18', NULL),

(18, 'FedEx', 'TRACK18', 40.00, 'Delayed', '2025-08-19', NULL),

(19, 'USPS', 'TRACK19', 3.50, 'Delivered', '2025-08-20', '2025-08-23'),

(20, 'DHL', 'TRACK20', 1.50, 'In Transit', '2025-08-21', NULL);

GO

-- Reviews (20 rows, referencing products 1-20, customers 1-20)

INSERT INTO Reviews (product_id, customer_id, rating, comment) VALUES 

(1, 1, 5, 'Great laptop!'),

(2, 2, 4, 'Comfortable T-shirt'),

(3, 3, 5, 'Amazing book'),

(4, 4, 3, 'Decent blender'),

(5, 5, 4, 'Good mat'),

(6, 6, 5, 'Nice lipstick'),

(7, 7, 2, 'Okay toy'),

(8, 8, 4, 'Solid tire'),

(9, 9, 5, 'Comfy sofa'),

(10, 10, 3, 'Average vitamins'),

(11, 11, 4, 'Tasty cereal'),

(12, 12, 5, 'Useful notebook'),

(13, 13, 4, 'Good dog food'),

(14, 14, 5, 'Excellent guitar'),

(15, 15, 3, 'Decent paint set'),

(16, 16, 4, 'Sturdy suitcase'),

(17, 17, 5, 'Beautiful necklace'),

(18, 18, 2, 'Okay bike'),

(19, 19, 4, 'Useful tools'),

(20, 20, 5, 'Protective case');

GO

-- Addresses (20 rows, referencing customers 1-20)

INSERT INTO Addresses (customer_id, type, street, city, state, zip_code, country) VALUES 

(1, 'Shipping', '123 Home St', 'New York', 'NY', '10001', 'USA'),

(2, 'Billing', '456 Apt Rd', 'Los Angeles', 'CA', '90001', 'USA'),

(3, 'Shipping', '789 Villa Ln', 'Chicago', 'IL', '60601', 'USA'),

(4, 'Billing', '101 Condo Blvd', 'Houston', 'TX', '77001', 'USA'),

(5, 'Shipping', '202 House Ave', 'Phoenix', 'AZ', '85001', 'USA'),

(6, 'Billing', '303 Flat Dr', 'Philadelphia', 'PA', '19101', 'USA'),

(7, 'Shipping', '404 Bungalow Way', 'San Antonio', 'TX', '78201', 'USA'),

(8, 'Billing', '505 Mansion Pl', 'San Diego', 'CA', '92101', 'USA'),

(9, 'Shipping', '606 Cottage Ct', 'Dallas', 'TX', '75201', 'USA'),

(10, 'Billing', '707 Estate Terr', 'San Jose', 'CA', '95101', 'USA'),

(11, 'Shipping', '808 Ranch Rd', 'Austin', 'TX', '78701', 'USA'),

(12, 'Billing', '909 Farm Ln', 'Jacksonville', 'FL', '32201', 'USA'),

(13, 'Shipping', '1010 City St', 'San Francisco', 'CA', '94101', 'USA'),

(14, 'Billing', '1111 Town Rd', 'Indianapolis', 'IN', '46201', 'USA'),

(15, 'Shipping', '1212 Village Ln', 'Columbus', 'OH', '43201', 'USA'),

(16, 'Billing', '1313 Suburb Blvd', 'Charlotte', 'NC', '28201', 'USA'),

(17, 'Shipping', '1414 Rural Ave', 'Fort Worth', 'TX', '76101', 'USA'),

(18, 'Billing', '1515 Urban Dr', 'Seattle', 'WA', '98101', 'USA'),

(19, 'Shipping', '1616 Metro Way', 'Denver', 'CO', '80201', 'USA'),

(20, 'Billing', '1717 Capital Pl', 'Washington', 'DC', '20001', 'USA');

GO

-- Coupons (20 rows)

INSERT INTO Coupons (code, discount_percentage, start_date, end_date, is_active) VALUES 

('SAVE10', 10.00, '2025-01-01', '2025-12-31', 1),

('FLASH20', 20.00, '2025-02-01', '2025-02-28', 1),

('SUMMER15', 15.00, '2025-06-01', '2025-08-31', 1),

('NEWUSER5', 5.00, '2025-01-01', '2025-12-31', 1),

('BLACKFRIDAY30', 30.00, '2025-11-25', '2025-11-30', 1),

('CYBERMONDAY25', 25.00, '2025-12-01', '2025-12-05', 1),

('HOLIDAY10', 10.00, '2025-12-15', '2025-12-25', 1),

('SPRINGDEAL', 12.00, '2025-03-01', '2025-05-31', 1),

('FALLSALE', 18.00, '2025-09-01', '2025-11-30', 1),

('WELCOME', 8.00, '2025-01-01', '2025-12-31', 1),

('LOYALTY', 22.00, '2025-04-01', '2025-04-30', 1),

('ENDYEAR', 35.00, '2025-12-26', '2025-12-31', 1),

('WEEKEND10', 10.00, '2025-01-01', '2025-12-31', 0),

('MIDYEAR20', 20.00, '2025-07-01', '2025-07-31', 1),

('BACKTOSCHOOL15', 15.00, '2025-08-01', '2025-09-30', 1),

('VIP5', 5.00, '2025-01-01', '2025-12-31', 1),

('HALLOWEEN30', 30.00, '2025-10-25', '2025-10-31', 1),

('THANKSGIVING25', 25.00, '2025-11-20', '2025-11-28', 1),

('EASTER10', 10.00, '2025-04-01', '2025-04-20', 0),

('LABORDAY15', 15.00, '2025-09-01', '2025-09-07', 1);

GO

Comprehensive Guide to SQL Server Stored Procedures Using an E-Commerce Database

Introduction

Stored procedures in SQL Server (T-SQL) are powerful, precompiled, server-side scripts that encapsulate business logic, improve query performance, and enhance security by reducing client-side code. This comprehensive guide uses the ECommerceDB database (from the provided T-SQL script with 11 tables and 20 rows each, as of September 8, 2025) to explore stored procedures in detail. We cover creation, input/output parameters, execution (including storing outputs in variables), complex operations, transactions, error handling, real-world e-commerce scenarios, use cases, best practices, and common pitfalls. The guide progresses through five complexity levels—Primary, Basic, Intermediate, Advanced, and Expert—each with multiple examples, detailed explanations, execution steps, and practical applications. Additionally, we include 10 scenario-based assignment questions and answers for trainers to test applicants, ensuring hands-on learning.

The ECommerceDB includes tables like Customers, Orders, Products, Order_Items, Payments, Sellers, Categories, Shipments, Reviews, Addresses, and Coupons, with constraints (e.g., PRIMARY KEY, FOREIGN KEY, CHECK) and sample data (e.g., 20 orders with total revenue of 2728.00). Scenarios are tailored to e-commerce tasks like order processing, inventory management, and customer analytics. All scripts are user-friendly, executable in SQL Server Management Studio (SSMS), and follow industry standards. Keywords include CREATE PROCEDURE, @parameters, IN/OUT, EXEC, BEGIN TRANSACTION, COMMIT, ROLLBACK, TRY-CATCH, ERROR_MESSAGE, and more. The content is SEO-optimized for terms like "T-SQL stored procedure tutorial," "SQL Server e-commerce examples," and "SQL transaction error handling."

---

E-Commerce Database Context

The ECommerceDB (created via ECommerceDB.sql) models an e-commerce platform with 11 tables:

• Customers: Stores user data (e.g., customer_id, username, email, is_active).
• Sellers: Manages vendor info (e.g., seller_id, business_name, rating).
• Categories: Organizes products (e.g., category_id, name like 'Electronics').
• Products: Holds item details (e.g., product_id, name, price, stock).
• Orders: Tracks purchases (e.g., order_id, customer_id, total_amount, status).
• Order_Items: Links orders to products (e.g., item_id, order_id, quantity).
• Payments: Records payments (e.g., payment_id, order_id, method).
• Shipments: Manages shipping (e.g., shipment_id, tracking_number).
• Reviews: Stores product feedback (e.g., review_id, rating, comment).
• Addresses: Holds customer addresses (e.g., address_id, type, street).
• Coupons: Manages discounts (e.g., coupon_id, code, discount_percentage).

Each table has 20 rows with varied data (e.g., order dates in August 2025, statuses like 'Pending', 'Delivered'). Constraints ensure data integrity (e.g., CHECK for rating BETWEEN 1 AND 5, UNIQUE for email). This setup supports realistic scenarios like checkout processing, inventory updates, and sales reporting.

---

Understanding Stored Procedures

A stored procedure is a named collection of T-SQL statements stored in the database, compiled once, and reusable for tasks like data manipulation, validation, or reporting. Benefits include:

• Performance: Precompiled execution plans reduce runtime overhead.
• Security: Restrict direct table access, allowing parameterized queries.
• Reusability: Encapsulate logic for consistent use across applications.
• Maintainability: Centralize code for easy updates.

Stored procedures are ideal for e-commerce tasks like processing orders, updating inventory, or generating reports. They support parameters (input/output), transactions for atomicity, and error handling for robustness. Common pitfalls include poor parameter validation, transaction mismanagement, and ignoring performance impacts (e.g., excessive locking).

---

Stored Procedure Complexity Levels: Step-by-Step Examples

Primary Level: Basic Creation and Execution

Purpose: Introduce stored procedure creation and execution without parameters, focusing on simple queries.

Example 1: List Active Customers

Scenario: Generate a list of active customers for a weekly marketing email campaign.

tsql

-- Create a procedure to list active customers CREATE PROCEDURE GetActiveCustomers AS BEGIN SET NOCOUNT ON; SELECT customer_id, username, email, first_name, last_name FROM Customers WHERE is_active = 1 ORDER BY username; END; GO -- Execute the procedure EXEC GetActiveCustomers;

Detailed Explanation:

• Purpose: Retrieves active customers (is_active = 1) for marketing, sorted by username for readability.
• Code Breakdown:
  • CREATE PROCEDURE GetActiveCustomers: Defines the procedure name.
  • SET NOCOUNT ON: Suppresses row count messages to reduce network traffic.
  • BEGIN/END: Encapsulates the query for clarity.
  • SELECT ... WHERE is_active = 1: Filters active customers (all 20 in sample data).
  • ORDER BY username: Ensures consistent output.
  • EXEC GetActiveCustomers: Runs the procedure.
• Execution Steps:
  1. Ensure ECommerceDB is active (USE ECommerceDB).
  2. Run the CREATE PROCEDURE script in SSMS.
  3. Execute with EXEC GetActiveCustomers.
• Storing Output in a Variable:
  tsql

  -- Store output in a temporary table CREATE TABLE #TempCustomers ( customer_id INT, username VARCHAR(50), email VARCHAR(100), first_name VARCHAR(50), last_name VARCHAR(50) ); INSERT INTO #TempCustomers EXEC GetActiveCustomers; SELECT * FROM #TempCustomers; DROP TABLE #TempCustomers;
  This stores results in #TempCustomers for further processing (e.g., exporting to a CRM).
• Output: 20 rows (e.g., 1, john_doe, john@example.com, John, Doe).
• Keywords: CREATE PROCEDURE, SET NOCOUNT, BEGIN, END, SELECT, WHERE, ORDER BY, EXEC, INSERT.
• Challenges:
  • Forgetting GO after CREATE PROCEDURE causes syntax errors.
  • Misspelling table/column names (e.g., Customer vs Customers).
  • Not understanding BIT type for is_active.
• Use Case: Automate weekly marketing reports, feeding customer data to email systems.
• Best Practices:
  • Use SET NOCOUNT ON to improve performance.
  • Include ORDER BY for predictable results.
  • Comment code for clarity (e.g., -- Marketing report).
  • Test with SELECT * FROM sys.procedures to verify creation.
• Variations:
  • Filter by preferred_language (e.g., WHERE preferred_language = 'English').
  • Add columns like registration_date for segmentation.
• Error Handling: Minimal at this level, but ensure table exists to avoid “Invalid object name” errors.

Why Primary: This is the simplest form, encapsulating a single query without parameters, ideal for beginners learning procedure syntax and execution.

---

Example 2: List Products by Category

Scenario: Display all products in a specific category (hard-coded) for a category page.

tsql

-- Create a procedure to list products in Electronics category CREATE PROCEDURE GetElectronicsProducts AS BEGIN SET NOCOUNT ON; SELECT p.product_id, p.name, p.price, p.stock FROM Products p INNER JOIN Categories c ON p.category_id = c.category_id WHERE c.name = 'Electronics'; END; GO -- Execute the procedure EXEC GetElectronicsProducts; -- Store output in a variable (table) CREATE TABLE #ElectronicsProducts ( product_id INT, name VARCHAR(100), price DECIMAL(10,2), stock INT ); INSERT INTO #ElectronicsProducts EXEC GetElectronicsProducts; SELECT * FROM #ElectronicsProducts; DROP TABLE #ElectronicsProducts;

Detailed Explanation:

• Purpose: Lists products in the 'Electronics' category for a website category page.
• Code Breakdown:
  • INNER JOIN: Links Products and Categories on category_id.
  • WHERE c.name = 'Electronics': Filters to one category.
  • SET NOCOUNT ON: Optimizes performance.
  • EXEC GetElectronicsProducts: Runs the query.
  • Temporary table stores output for further use (e.g., API response).
• Execution Steps:
  1. Run CREATE PROCEDURE in SSMS.
  2. Execute with EXEC GetElectronicsProducts.
  3. Use the temporary table script to capture output.
• Output: 1 row (e.g., 1, Laptop, 1200.00, 50).
• Keywords: CREATE PROCEDURE, SET NOCOUNT, BEGIN, END, SELECT, INNER JOIN, WHERE, EXEC, INSERT.
• Challenges:
  • Ensuring correct JOIN syntax to avoid Cartesian products.
  • Handling case sensitivity in WHERE c.name = 'Electronics'.
  • Verifying category_id FK integrity.
• Use Case: Populate e-commerce category pages or inventory reports.
• Best Practices:
  • Use specific column names (not *) for clarity.
  • Validate category existence before execution.
  • Store in a schema (e.g., dbo.GetElectronicsProducts).
• Variations:
  • Include description or image_url for richer output.
  • Filter by is_available = 1 for active products.
• Error Handling: Check for category existence to avoid empty results.

---

Basic Level: Input Parameters

Purpose: Introduce input parameters for dynamic queries.

Example 3: Get Orders by Customer

Scenario: Retrieve all orders for a specific customer to display their order history.

tsql

-- Create a procedure with an input parameter CREATE PROCEDURE GetCustomerOrders @CustomerID INT AS BEGIN SET NOCOUNT ON; SELECT order_id, order_date, total_amount, status FROM Orders WHERE customer_id = @CustomerID ORDER BY order_date DESC; END; GO -- Execute with different inputs EXEC GetCustomerOrders @CustomerID = 1; -- Orders for john_doe EXEC GetCustomerOrders @CustomerID = 2; -- Orders for jane_smith -- Store output in a variable (table) CREATE TABLE #CustomerOrders ( order_id INT, order_date DATETIME, total_amount DECIMAL(10,2), status VARCHAR(20) ); INSERT INTO #CustomerOrders EXEC GetCustomerOrders @CustomerID = 1; SELECT * FROM #CustomerOrders; DROP TABLE #CustomerOrders; -- Basic error handling DECLARE @CustomerID INT = 999; -- Non-existent ID IF NOT EXISTS (SELECT 1 FROM Customers WHERE customer_id = @CustomerID) BEGIN RAISERROR ('Customer ID %d does not exist', 16, 1, @CustomerID); RETURN; END EXEC GetCustomerOrders @CustomerID;

Detailed Explanation:

• Purpose: Displays a customer’s order history for a user portal, filtered by customer_id.
• Code Breakdown:
  • @CustomerID INT: Input parameter for dynamic filtering.
  • WHERE customer_id = @CustomerID: Filters orders.
  • ORDER BY order_date DESC: Shows recent orders first.
  • RAISERROR: Basic error handling for invalid IDs.
  • Temporary table captures output for further processing.
• Execution Steps:
  1. Create the procedure in SSMS.
  2. Execute with EXEC GetCustomerOrders @CustomerID = 1.
  3. Use the temporary table script to store results.
  4. Test error handling with an invalid @CustomerID.
• Output: For @CustomerID = 1, 1 row (e.g., 1, 2025-08-01, 1200.00, Delivered).
• Keywords: CREATE PROCEDURE, @parameter, SET NOCOUNT, SELECT, WHERE, ORDER BY, EXEC, RAISERROR, IF, INSERT.
• Challenges:
  • Validating @CustomerID against Customers to avoid empty results.
  • Parameter type mismatches (e.g., passing a string).
  • Handling FOREIGN KEY constraints.
• Use Case: Customer order history in an e-commerce dashboard.
• Best Practices:
  • Validate input parameters to prevent invalid queries.
  • Use descriptive parameter names (e.g., @CustomerID vs @ID).
  • Include ORDER BY for user-friendly output.
• Variations:
  • Add a date range parameter (e.g., @StartDate, @EndDate).
  • Include Order_Items via JOIN for detailed order data.
• Error Handling: Check for customer existence to avoid silent failures.

---

Example 4: Filter Products by Price Range

Scenario: List products within a specified price range for a filtered product search.

tsql

-- Create a procedure with multiple input parameters CREATE PROCEDURE GetProductsByPrice @MinPrice DECIMAL(10,2), @MaxPrice DECIMAL(10,2) AS BEGIN SET NOCOUNT ON; IF @MinPrice < 0 OR @MaxPrice < @MinPrice BEGIN RAISERROR ('Invalid price range: MinPrice=%d, MaxPrice=%d', 16, 1, @MinPrice, @MaxPrice); RETURN; END SELECT product_id, name, price, stock FROM Products WHERE price BETWEEN @MinPrice AND @MaxPrice ORDER BY price; END; GO -- Execute with different inputs EXEC GetProductsByPrice @MinPrice = 10.00, @MaxPrice = 50.00; EXEC GetProductsByPrice @MinPrice = 0.00, @MaxPrice = 100.00; -- Store output in a variable (table) CREATE TABLE #PriceFilteredProducts ( product_id INT, name VARCHAR(100), price DECIMAL(10,2), stock INT ); INSERT INTO #PriceFilteredProducts EXEC GetProductsByPrice @MinPrice = 10.00, @MaxPrice = 50.00; SELECT * FROM #PriceFilteredProducts; DROP TABLE #PriceFilteredProducts; -- Test error handling EXEC GetProductsByPrice @MinPrice = 100.00, @MaxPrice = 50.00; -- Invalid range

Detailed Explanation:

• Purpose: Filters products for a price-based search feature on an e-commerce site.
• Code Breakdown:
  • @MinPrice, @MaxPrice: Input parameters for price range.
  • IF ... RAISERROR: Validates price range (non-negative, logical order).
  • WHERE price BETWEEN ...: Filters products inclusively.
  • Temporary table stores results for further use (e.g., JSON export).
• Execution Steps:
  1. Create the procedure.
  2. Run with valid inputs (e.g., 10.00, 50.00).
  3. Capture output in a temporary table.
  4. Test error handling with invalid inputs.
• Output: For @MinPrice = 10.00, @MaxPrice = 50.00, 13 rows (e.g., 2, T-Shirt, 20.00, 100).
• Keywords: CREATE PROCEDURE, @parameter, SET NOCOUNT, IF, RAISERROR, SELECT, WHERE, BETWEEN, ORDER BY, EXEC, INSERT.
• Challenges:
  • Ensuring valid price range to avoid logical errors.
  • Handling DECIMAL precision in comparisons.
  • Avoiding empty results for narrow ranges.
• Use Case: Price filter on product search pages.
• Best Practices:
  • Validate parameter logic early.
  • Use BETWEEN for readable range queries.
  • Store results for integration with APIs.
• Variations:
  • Add @CategoryID to filter by category.
  • Include is_available = 1 for active products.
• Error Handling: Validate input ranges to prevent invalid queries.

---

Intermediate Level: Input/Output Parameters and Simple Operations

Purpose: Use input and output parameters to perform data modifications and return results.

Example 5: Add New Order

Scenario: Insert a new order and return the generated order ID for further processing.

tsql

-- Create a procedure with input and output parameters CREATE PROCEDURE AddNewOrder @CustomerID INT, @TotalAmount DECIMAL(10,2), @ShippingAddress VARCHAR(255), @BillingAddress VARCHAR(255), @NewOrderID INT OUTPUT AS BEGIN SET NOCOUNT ON; IF NOT EXISTS (SELECT 1 FROM Customers WHERE customer_id = @CustomerID) BEGIN RAISERROR ('Invalid Customer ID %d', 16, 1, @CustomerID); RETURN; END IF @TotalAmount <= 0 BEGIN RAISERROR ('Total amount must be positive: %d', 16, 1, @TotalAmount); RETURN; END INSERT INTO Orders (customer_id, total_amount, shipping_address, billing_address, status) VALUES (@CustomerID, @TotalAmount, @ShippingAddress, @BillingAddress, 'Pending'); SET @NewOrderID = SCOPE_IDENTITY(); END; GO -- Execute and capture output DECLARE @OrderID INT; EXEC AddNewOrder @CustomerID = 2, @TotalAmount = 150.00, @ShippingAddress = '456 Apt Rd', @BillingAddress = '456 Apt Rd', @NewOrderID = @OrderID OUTPUT; SELECT @OrderID AS NewOrderID; -- Test error handling DECLARE @ErrorOrderID INT; EXEC AddNewOrder @CustomerID = 999, -- Invalid ID @TotalAmount = 150.00, @ShippingAddress = '456 Apt Rd', @BillingAddress = '456 Apt Rd', @NewOrderID = @ErrorOrderID OUTPUT;

Detailed Explanation:

• Purpose: Creates a new order for a customer and returns the order_id for adding items or payments.
• Code Breakdown:
  • @CustomerID, @TotalAmount, etc.: Input parameters for order details.
  • @NewOrderID INT OUTPUT: Returns the new order_id.
  • IF NOT EXISTS: Validates customer existence.
  • IF @TotalAmount <= 0: Ensures valid amount.
  • INSERT INTO Orders: Adds the order with Pending status.
  • SCOPE_IDENTITY(): Captures the new order_id.
• Execution Steps:
  1. Create the procedure in SSMS.
  2. Declare @OrderID to store output.
  3. Execute with valid inputs (e.g., @CustomerID = 2).
  4. Test with invalid @CustomerID to trigger error.
• Output: NewOrderID = 21 (next ID after 20 orders).
• Keywords: CREATE PROCEDURE, @parameter, OUTPUT, SET NOCOUNT, IF, RAISERROR, INSERT, SCOPE_IDENTITY, EXEC, SELECT.
• Challenges:
  • Ensuring @CustomerID satisfies FOREIGN KEY constraint.
  • Capturing OUTPUT parameter correctly.
  • Handling invalid inputs (e.g., negative @TotalAmount).
• Use Case: Order creation during checkout, returning ID for subsequent steps.
• Best Practices:
  • Validate all inputs to prevent constraint violations.
  • Use SCOPE_IDENTITY() for reliable ID retrieval.
  • Return meaningful error messages.
• Variations:
  • Add @Status parameter with CHECK validation.
  • Include discount_applied in the insert.
• Error Handling: Checks for customer existence and valid amounts to avoid FK or logical errors.

---

Example 6: Update Product Stock

Scenario: Update product stock after a manual inventory adjustment and return the new stock level.

tsql

-- Create a procedure to update stock and return new value CREATE PROCEDURE UpdateProductStock @ProductID INT, @StockAdjustment INT, @NewStock INT OUTPUT AS BEGIN SET NOCOUNT ON; IF NOT EXISTS (SELECT 1 FROM Products WHERE product_id = @ProductID) BEGIN RAISERROR ('Invalid Product ID %d', 16, 1, @ProductID); RETURN; END UPDATE Products SET stock = stock + @StockAdjustment, @NewStock = stock + @StockAdjustment WHERE product_id = @ProductID; IF @NewStock < 0 BEGIN RAISERROR ('Stock cannot be negative for Product ID %d', 16, 1, @ProductID); RETURN; END END; GO -- Execute and capture output DECLARE @NewStockLevel INT; EXEC UpdateProductStock @ProductID = 1, @StockAdjustment = -5, @NewStock = @NewStockLevel OUTPUT; SELECT @NewStockLevel AS NewStock; -- Test error handling DECLARE @ErrorStock INT; EXEC UpdateProductStock @ProductID = 1, @StockAdjustment = -100, -- Would make stock negative @NewStock = @ErrorStock OUTPUT;

Detailed Explanation:

• Purpose: Adjusts product stock (e.g., after restocking or returns) and returns the new stock level.
• Code Breakdown:
  • @ProductID, @StockAdjustment: Inputs for product and adjustment amount.
  • @NewStock INT OUTPUT: Returns updated stock.
  • IF NOT EXISTS: Validates product.
  • UPDATE ... SET stock = ... , @NewStock = ...: Updates and captures new stock.
  • IF @NewStock < 0: Prevents negative stock (aligned with CHECK constraint).
• Execution Steps:
  1. Create the procedure.
  2. Declare @NewStockLevel for output.
  3. Execute with valid adjustment (e.g., -5 for Laptop).
  4. Test with invalid adjustment (e.g., -100) to trigger error.
• Output: NewStock = 45 (Laptop stock from 50 to 45).
• Keywords: CREATE PROCEDURE, @parameter, OUTPUT, SET NOCOUNT, IF, RAISERROR, UPDATE, EXEC, SELECT.
• Challenges:
  • Aligning with CHECK (stock >= 0) constraint.
  • Capturing output in UPDATE statement.
  • Handling concurrent updates.
• Use Case: Inventory adjustments in e-commerce (e.g., restocking, returns).
• Best Practices:
  • Validate inputs against constraints.
  • Use output parameters for feedback.
  • Check post-update conditions (e.g., negative stock).
• Variations:
  • Add @Reason parameter for audit logging.
  • Include updated_at update in Products.
• Error Handling: Prevents invalid products and negative stock, aligning with DB constraints.

---

Advanced Level: Complex Operations and Transactions

Purpose: Incorporate multi-table updates, transactions, and basic error handling.

Example 7: Add Order Item with Stock Update

Scenario: Add an order item and update product stock in a single transaction.

tsql

-- Create a procedure with transaction for order item and stock CREATE PROCEDURE AddOrderItem @OrderID INT, @ProductID INT, @Quantity INT, @PricePerUnit DECIMAL(10,2) AS BEGIN SET NOCOUNT ON; BEGIN TRANSACTION; BEGIN TRY -- Validate inputs IF NOT EXISTS (SELECT 1 FROM Orders WHERE order_id = @OrderID) BEGIN RAISERROR ('Invalid Order ID %d', 16, 1, @OrderID); RETURN; END IF NOT EXISTS (SELECT 1 FROM Products WHERE product_id = @ProductID AND stock >= @Quantity) BEGIN RAISERROR ('Invalid Product ID %d or insufficient stock', 16, 1, @ProductID); RETURN; END IF @Quantity <= 0 BEGIN RAISERROR ('Quantity must be positive: %d', 16, 1, @Quantity); RETURN; END -- Update product stock UPDATE Products SET stock = stock - @Quantity WHERE product_id = @ProductID; -- Insert order item INSERT INTO Order_Items (order_id, product_id, quantity, price_per_unit, subtotal) VALUES (@OrderID, @ProductID, @Quantity, @PricePerUnit, @Quantity * @PricePerUnit); COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; DECLARE @ErrorMsg NVARCHAR(4000) = ERROR_MESSAGE(); RAISERROR (@ErrorMsg, 16, 1); END CATCH; END; GO -- Execute the procedure EXEC AddOrderItem @OrderID = 1, @ProductID = 2, @Quantity = 2, @PricePerUnit = 20.00; -- Test error handling EXEC AddOrderItem @OrderID = 999, -- Invalid order @ProductID = 2, @Quantity = 2, @PricePerUnit = 20.00;

Detailed Explanation:

• Purpose: Adds an order item and reduces stock atomically, ensuring inventory consistency during checkout.
• Code Breakdown:
  • @OrderID, @ProductID, etc.: Inputs for order item details.
  • BEGIN TRANSACTION: Ensures atomicity.
  • BEGIN TRY/CATCH: Catches errors (e.g., FK violations).
  • IF NOT EXISTS: Validates order and product with sufficient stock.
  • UPDATE Products: Reduces stock.
  • INSERT Order_Items: Adds item with calculated subtotal.
  • COMMIT: Finalizes transaction.
  • ROLLBACK in CATCH: Undoes changes on error.
• Execution Steps:
  1. Create the procedure.
  2. Execute with valid inputs (e.g., order_id = 1, product_id = 2).
  3. Test with invalid @OrderID to trigger error.
• Output: 1 row inserted into Order_Items, T-Shirt stock reduced from 100 to 98.
• Keywords: CREATE PROCEDURE, @parameter, SET NOCOUNT, BEGIN TRANSACTION, COMMIT, ROLLBACK, BEGIN TRY, BEGIN CATCH, RAISERROR, ERROR_MESSAGE, IF, UPDATE, INSERT.
• Challenges:
  • Ensuring transaction scope (all or nothing).
  • Handling CHECK constraints (e.g., quantity > 0).
  • Managing concurrent stock updates (e.g., use UPDLOCK).
• Use Case: Adding items to a cart during checkout.
• Best Practices:
  • Use transactions for multi-table updates.
  • Validate all inputs before modifications.
  • Capture errors with TRY-CATCH for user feedback.
• Variations:
  • Add @Discount parameter for subtotal calculation.
  • Update Orders.total_amount dynamically.
• Error Handling: Robust validation and TRY-CATCH handle FK, stock, and quantity errors.

---

Example 8: Apply Coupon to Order

Scenario: Apply a coupon to an order and update the discount, ensuring the coupon is valid.

tsql

-- Create a procedure to apply a coupon CREATE PROCEDURE ApplyCoupon @OrderID INT, @CouponCode VARCHAR(20), @DiscountApplied DECIMAL(10,2) OUTPUT AS BEGIN SET NOCOUNT ON; BEGIN TRANSACTION; BEGIN TRY -- Validate inputs IF NOT EXISTS (SELECT 1 FROM Orders WHERE order_id = @OrderID) BEGIN RAISERROR ('Invalid Order ID %d', 16, 1, @OrderID); RETURN; END IF NOT EXISTS (SELECT 1 FROM Coupons WHERE code = @CouponCode AND is_active = 1 AND GETDATE() BETWEEN start_date AND end_date) BEGIN RAISERROR ('Invalid or expired Coupon Code %s', 16, 1, @CouponCode); RETURN; END -- Calculate discount DECLARE @DiscountPercentage DECIMAL(5,2); SELECT @DiscountPercentage = discount_percentage FROM Coupons WHERE code = @CouponCode; UPDATE Orders SET discount_applied = total_amount * (@DiscountPercentage / 100), @DiscountApplied = total_amount * (@DiscountPercentage / 100) WHERE order_id = @OrderID; -- Update coupon usage UPDATE Coupons SET used_count = used_count + 1 WHERE code = @CouponCode; COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; DECLARE @ErrorMsg NVARCHAR(4000) = ERROR_MESSAGE(); RAISERROR (@ErrorMsg, 16, 1); END CATCH; END; GO -- Execute and capture output DECLARE @Discount DECIMAL(10,2); EXEC ApplyCoupon @OrderID = 3, @CouponCode = 'SUMMER15', @DiscountApplied = @Discount OUTPUT; SELECT @Discount AS DiscountApplied; -- Test error handling EXEC ApplyCoupon @OrderID = 3, @CouponCode = 'EXPIRED'; -- Invalid coupon

Detailed Explanation:

• Purpose: Applies a coupon to an order, calculates the discount, and tracks coupon usage.
• Code Breakdown:
  • @OrderID, @CouponCode: Inputs for order and coupon.
  • @DiscountApplied OUTPUT: Returns the discount amount.
  • IF NOT EXISTS: Validates order and active coupon within date range.
  • DECLARE @DiscountPercentage: Stores coupon discount rate.
  • UPDATE Orders: Applies discount based on total_amount.
  • UPDATE Coupons: Increments used_count.
  • TRY-CATCH: Handles errors like invalid coupons.
• Execution Steps:
  1. Create the procedure.
  2. Execute with valid @OrderID and @CouponCode (e.g., SUMMER15).
  3. Capture @DiscountApplied.
  4. Test with invalid coupon code.
• Output: For order_id = 3, DiscountApplied = 2.25 (15% of 15.00).
• Keywords: CREATE PROCEDURE, @parameter, OUTPUT, SET NOCOUNT, BEGIN TRANSACTION, COMMIT, ROLLBACK, BEGIN TRY, BEGIN CATCH, RAISERROR, ERROR_MESSAGE, IF, UPDATE, SELECT.
• Challenges:
  • Validating coupon date range and activity.
  • Calculating discount correctly.
  • Handling concurrent coupon usage.
• Use Case: Coupon application during checkout.
• Best Practices:
  • Validate date ranges dynamically.
  • Use transactions for multi-table updates.
  • Return calculated values via output parameters.
• Variations:
  • Check usage_limit in Coupons.
  • Update Orders.total_amount after discount.
• Error Handling: Ensures valid order and coupon, rolls back on failure.

---

Expert Level: Complex Operations, Transactions, and Robust Error Handling

Purpose: Handle complex multi-table operations, full transaction management, and advanced error handling.

Example 9: Process Complete Order

Scenario: Process a full order (insert order, order items, payment, update stock) with comprehensive validation.

tsql

-- Create a procedure for complete order processing CREATE PROCEDURE ProcessOrder @CustomerID INT, @ProductID INT, @Quantity INT, @PricePerUnit DECIMAL(10,2), @ShippingAddress VARCHAR(255), @BillingAddress VARCHAR(255), @PaymentMethod VARCHAR(20), @CardLastFour VARCHAR(4), @CVV VARCHAR(3), @BillingEmail VARCHAR(100), @NewOrderID INT OUTPUT, @ErrorMessage NVARCHAR(4000) OUTPUT AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION; -- Validate inputs IF NOT EXISTS (SELECT 1 FROM Customers WHERE customer_id = @CustomerID) BEGIN SET @ErrorMessage = 'Invalid Customer ID ' + CAST(@CustomerID AS VARCHAR(10)); RAISERROR (@ErrorMessage, 16, 1); RETURN; END IF NOT EXISTS (SELECT 1 FROM Products WHERE product_id = @ProductID AND stock >= @Quantity) BEGIN SET @ErrorMessage = 'Invalid Product ID or insufficient stock for Product ID ' + CAST(@ProductID AS VARCHAR(10)); RAISERROR (@ErrorMessage, 16, 1); RETURN; END IF @Quantity <= 0 BEGIN SET @ErrorMessage = 'Quantity must be positive: ' + CAST(@Quantity AS VARCHAR(10)); RAISERROR (@ErrorMessage, 16, 1); RETURN; END IF @PaymentMethod NOT IN ('Credit Card', 'PayPal', 'Bank Transfer') BEGIN SET @ErrorMessage = 'Invalid Payment Method: ' + @PaymentMethod; RAISERROR (@ErrorMessage, 16, 1); RETURN; END IF LEN(@CVV) != 3 BEGIN SET @ErrorMessage = 'Invalid CVV length'; RAISERROR (@ErrorMessage, 16, 1); RETURN; END -- Insert new order INSERT INTO Orders (customer_id, total_amount, shipping_address, billing_address, status) VALUES (@CustomerID, @Quantity * @PricePerUnit, @ShippingAddress, @BillingAddress, 'Pending'); SET @NewOrderID = SCOPE_IDENTITY(); -- Insert order item INSERT INTO Order_Items (order_id, product_id, quantity, price_per_unit, subtotal) VALUES (@NewOrderID, @ProductID, @Quantity, @PricePerUnit, @Quantity * @PricePerUnit); -- Update product stock UPDATE Products SET stock = stock - @Quantity WHERE product_id = @ProductID; -- Insert payment INSERT INTO Payments (order_id, amount, method, status, transaction_id, card_last_four, cvv, billing_email) VALUES (@NewOrderID, @Quantity * @PricePerUnit, @PaymentMethod, 'Pending', 'TX' + CAST(@NewOrderID AS VARCHAR(10)), @CardLastFour, @CVV, @BillingEmail); COMMIT; SET @ErrorMessage = NULL; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; SET @ErrorMessage = ERROR_MESSAGE(); RAISERROR (@ErrorMessage, 16, 1); END CATCH; END; GO -- Execute and capture output DECLARE @OrderID INT, @Error NVARCHAR(4000); EXEC ProcessOrder @CustomerID = 1, @ProductID = 2, @Quantity = 3, @PricePerUnit = 20.00, @ShippingAddress = '123 Home St', @BillingAddress = '123 Home St', @PaymentMethod = 'Credit Card', @CardLastFour = '1234', @CVV = '123', @BillingEmail = 'john@example.com', @NewOrderID = @OrderID OUTPUT, @ErrorMessage = @Error OUTPUT; SELECT @OrderID AS NewOrderID, @Error AS ErrorMessage; -- Test error handling EXEC ProcessOrder @CustomerID = 999, -- Invalid customer @ProductID = 2, @Quantity = 3, @PricePerUnit = 20.00, @ShippingAddress = '123 Home St', @BillingAddress = '123 Home St', @PaymentMethod = 'Credit Card', @CardLastFour = '1234', @CVV = '123', @BillingEmail = 'john@example.com', @NewOrderID = @OrderID OUTPUT, @ErrorMessage = @Error OUTPUT;

Detailed Explanation:

• Purpose: Processes a complete e-commerce order, updating Orders, Order_Items, Products, and Payments atomically.
• Code Breakdown:
  • Multiple input parameters for order details.
  • @NewOrderID, @ErrorMessage OUTPUT: Return order ID and error details.
  • Extensive validation for customer, product, quantity, payment method, and CVV.
  • BEGIN TRY/CATCH: Captures errors (e.g., FK violations, CHECK constraints).
  • BEGIN TRANSACTION: Ensures all operations succeed or fail together.
  • INSERT Orders, Order_Items, Payments: Adds order data.
  • UPDATE Products: Reduces stock.
  • COMMIT/ROLLBACK: Manages transaction outcome.
• Execution Steps:
  1. Create the procedure.
  2. Declare variables for outputs.
  3. Execute with valid inputs.
  4. Test with invalid inputs (e.g., @CustomerID = 999).
• Output: NewOrderID = 21, ErrorMessage = NULL (success); or error message for failures.
• Keywords: CREATE PROCEDURE, @parameter, OUTPUT, SET NOCOUNT, BEGIN TRY, BEGIN CATCH, BEGIN TRANSACTION, COMMIT, ROLLBACK, RAISERROR, ERROR_MESSAGE, IF, INSERT, UPDATE, SCOPE_IDENTITY.
• Challenges:
  • Coordinating multi-table updates.
  • Handling CHECK constraints (e.g., cvv length, status values).
  • Managing transaction isolation for concurrency.
• Use Case: Full checkout process in e-commerce.
• Best Practices:
  • Validate all inputs against constraints.
  • Use TRY-CATCH for robust error handling.
  • Consider locking (e.g., UPDLOCK) for stock updates.
• Variations:
  • Add coupon application logic.
  • Include Shipments insertion.
• Error Handling: Comprehensive validation and error capture ensure robust execution.

---

Example 10: Process Bulk Order with Multiple Items

Scenario: Process an order with multiple items, updating stock and payments, with detailed error logging.

tsql

-- Create a procedure for bulk order processing CREATE PROCEDURE ProcessBulkOrder @CustomerID INT, @ShippingAddress VARCHAR(255), @BillingAddress VARCHAR(255), @PaymentMethod VARCHAR(20), @CardLastFour VARCHAR(4), @CVV VARCHAR(3), @BillingEmail VARCHAR(100), @NewOrderID INT OUTPUT, @ErrorMessage NVARCHAR(4000) OUTPUT AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION; -- Validate customer and payment IF NOT EXISTS (SELECT 1 FROM Customers WHERE customer_id = @CustomerID) BEGIN SET @ErrorMessage = 'Invalid Customer ID ' + CAST(@CustomerID AS VARCHAR(10)); RAISERROR (@ErrorMessage, 16, 1); RETURN; END IF @PaymentMethod NOT IN ('Credit Card', 'PayPal', 'Bank Transfer') BEGIN SET @ErrorMessage = 'Invalid Payment Method: ' + @PaymentMethod; RAISERROR (@ErrorMessage, 16, 1); RETURN; END IF LEN(@CVV) != 3 BEGIN SET @ErrorMessage = 'Invalid CVV length'; RAISERROR (@ErrorMessage, 16, 1); RETURN; END -- Create a temporary table for items CREATE TABLE #OrderItems ( ProductID INT, Quantity INT, PricePerUnit DECIMAL(10,2) ); -- Example: Insert sample items (in real use, passed via table-valued parameter) INSERT INTO #OrderItems (ProductID, Quantity, PricePerUnit) VALUES (1, 2, 1200.00), (2, 3, 20.00); -- Validate items IF EXISTS ( SELECT 1 FROM #OrderItems oi LEFT JOIN Products p ON oi.ProductID = p.product_id WHERE p.product_id IS NULL OR p.stock < oi.Quantity OR oi.Quantity <= 0 ) BEGIN SET @ErrorMessage = 'Invalid items or insufficient stock'; RAISERROR (@ErrorMessage, 16, 1); RETURN; END -- Calculate total amount DECLARE @TotalAmount DECIMAL(10,2); SELECT @TotalAmount = SUM(Quantity * PricePerUnit) FROM #OrderItems; -- Insert order INSERT INTO Orders (customer_id, total_amount, shipping_address, billing_address, status) VALUES (@CustomerID, @TotalAmount, @ShippingAddress, @BillingAddress, 'Pending'); SET @NewOrderID = SCOPE_IDENTITY(); -- Insert order items INSERT INTO Order_Items (order_id, product_id, quantity, price_per_unit, subtotal) SELECT @NewOrderID, ProductID, Quantity, PricePerUnit, Quantity * PricePerUnit FROM #OrderItems; -- Update product stock UPDATE Products SET stock = stock - oi.Quantity FROM Products p INNER JOIN #OrderItems oi ON p.product_id = oi.ProductID; -- Insert payment INSERT INTO Payments (order_id, amount, method, status, transaction_id, card_last_four, cvv, billing_email) VALUES (@NewOrderID, @TotalAmount, @PaymentMethod, 'Pending', 'TX' + CAST(@NewOrderID AS VARCHAR(10)), @CardLastFour, @CVV, @BillingEmail); DROP TABLE #OrderItems; COMMIT; SET @ErrorMessage = NULL; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; SET @ErrorMessage = ERROR_MESSAGE(); RAISERROR (@ErrorMessage, 16, 1); END CATCH; END; GO -- Execute and capture output DECLARE @OrderID INT, @Error NVARCHAR(4000); EXEC ProcessBulkOrder @CustomerID = 1, @ShippingAddress = '123 Home St', @BillingAddress = '123 Home St', @PaymentMethod = 'Credit Card', @CardLastFour = '1234', @CVV = '123', @BillingEmail = 'john@example.com', @NewOrderID = @OrderID OUTPUT, @ErrorMessage = @Error OUTPUT; SELECT @OrderID AS NewOrderID, @Error AS ErrorMessage; -- Test error handling EXEC ProcessBulkOrder @CustomerID = 1, @ShippingAddress = '123 Home St', @BillingAddress = '123 Home St', @PaymentMethod = 'Invalid', -- Invalid method @CardLastFour = '1234', @CVV = '123', @BillingEmail = 'john@example.com', @NewOrderID = @OrderID OUTPUT, @ErrorMessage = @Error OUTPUT;

Detailed Explanation:

• Purpose: Processes an order with multiple items, updating stock and payments in a single transaction.
• Code Breakdown:
  • Inputs for customer, payment, and order details; outputs for order_id and errors.
  • #OrderItems: Temporary table for multiple items (simulating a cart).
  • Extensive validation for customer, payment, and items.
  • SELECT SUM: Calculates total order amount.
  • Inserts into Orders, Order_Items, Payments; updates Products.
  • TRY-CATCH and ROLLBACK for error handling.
• Execution Steps:
  1. Create the procedure.
  2. Execute with valid inputs.
  3. Test with invalid payment method.
• Output: NewOrderID = 21, ErrorMessage = NULL (success).
• Keywords: CREATE PROCEDURE, @parameter, OUTPUT, SET NOCOUNT, BEGIN TRY, BEGIN CATCH, BEGIN TRANSACTION, COMMIT, ROLLBACK, RAISERROR, ERROR_MESSAGE, IF, INSERT, UPDATE, SELECT, CREATE TABLE, DROP TABLE.
• Challenges:
  • Managing multiple items in a transaction.
  • Validating temporary table data.
  • Handling concurrency for stock updates.
• Use Case: Bulk order processing during checkout.
• Best Practices:
  • Use temporary tables for bulk data.
  • Validate all inputs thoroughly.
  • Ensure transaction isolation (e.g., SERIALIZABLE).
• Variations:
  • Use table-valued parameters for items.
  • Add coupon discounts.
• Error Handling: Comprehensive validation and error capture for robustness.

---

10 Scenario-Based Assignment Questions and Answers

These assignments test stored procedure skills using the ECommerceDB, focusing on creation, parameters, execution, transactions, and error handling. Each includes a scenario, solution, explanation, execution, output, and use case.

Question 1: List Products by Category (Primary)

Scenario: Create a procedure to list products for a given category ID for a website category page.

tsql

CREATE PROCEDURE GetProductsByCategory @CategoryID INT AS BEGIN SET NOCOUNT ON; SELECT p.product_id, p.name, p.price, p.stock FROM Products p INNER JOIN Categories c ON p.category_id = c.category_id WHERE c.category_id = @CategoryID; END; GO -- Execute EXEC GetProductsByCategory @CategoryID = 1; -- Store output CREATE TABLE #CategoryProducts ( product_id INT, name VARCHAR(100), price DECIMAL(10,2), stock INT ); INSERT INTO #CategoryProducts EXEC GetProductsByCategory @CategoryID = 1; SELECT * FROM #CategoryProducts; DROP TABLE #CategoryProducts;

Explanation:

• Purpose: Displays products for a category (e.g., Electronics).
• Details: Uses @CategoryID to filter products via INNER JOIN. Stores output in a temporary table.
• Execution: Run EXEC with @CategoryID = 1 (Electronics).
• Output: 1 row (e.g., 1, Laptop, 1200.00, 50).
• Use Case: Category browsing on e-commerce sites.
• Challenges: Ensuring valid @CategoryID, handling empty results.
• Best Practices: Use SET NOCOUNT ON, validate category existence.

Question 2: Add Customer (Basic)

Scenario: Insert a new customer and return their ID.

tsql

CREATE PROCEDURE AddCustomer @Username VARCHAR(50), @Email VARCHAR(100), @PasswordHash VARCHAR(255), @FirstName VARCHAR(50), @LastName VARCHAR(50), @NewCustomerID INT OUTPUT AS BEGIN SET NOCOUNT ON; IF EXISTS (SELECT 1 FROM Customers WHERE username = @Username OR email = @Email) BEGIN RAISERROR ('Username or email already exists', 16, 1); RETURN; END INSERT INTO Customers (username, email, password_hash, first_name, last_name) VALUES (@Username, @Email, @PasswordHash, @FirstName, @LastName); SET @NewCustomerID = SCOPE_IDENTITY(); END; GO -- Execute DECLARE @CustomerID INT; EXEC AddCustomer @Username = 'new_customer', @Email = 'new@customer.com', @PasswordHash = 'hash22', @FirstName = 'New', @LastName = 'Customer', @NewCustomerID = @CustomerID OUTPUT; SELECT @CustomerID AS NewCustomerID;

Explanation:

• Purpose: Registers a new customer.
• Details: Validates UNIQUE constraints, inserts, and returns customer_id.
• Execution: Run with unique @Username, @Email.
• Output: NewCustomerID = 21.
• Use Case: User registration.
• Challenges: Handling UNIQUE violations.
• Best Practices: Validate inputs, use SCOPE_IDENTITY().

Question 3: Update Order Status (Intermediate)

Scenario: Update an order’s status and return the new status.

tsql

CREATE PROCEDURE UpdateOrderStatus @OrderID INT, @NewStatus VARCHAR(20), @CurrentStatus VARCHAR(20) OUTPUT AS BEGIN SET NOCOUNT ON; IF NOT EXISTS (SELECT 1 FROM Orders WHERE order_id = @OrderID) BEGIN RAISERROR ('Invalid Order ID %d', 16, 1, @OrderID); RETURN; END IF @NewStatus NOT IN ('Pending', 'Shipped', 'Delivered', 'Cancelled') BEGIN RAISERROR ('Invalid Status %s', 16, 1, @NewStatus); RETURN; END UPDATE Orders SET status = @NewStatus, @CurrentStatus = @NewStatus WHERE order_id = @OrderID; END; GO -- Execute DECLARE @Status VARCHAR(20); EXEC UpdateOrderStatus @OrderID = 1, @NewStatus = 'Shipped', @CurrentStatus = @Status OUTPUT; SELECT @Status AS CurrentStatus;

Explanation:

• Purpose: Updates order status (e.g., to 'Shipped').
• Details: Validates order and status, updates Orders.
• Execution: Run with valid @OrderID, @NewStatus.
• Output: CurrentStatus = Shipped.
• Use Case: Order tracking updates.
• Challenges: Aligning with CHECK constraint.
• Best Practices: Validate status values.

Question 4: Delete Expired Coupons (Intermediate)

Scenario: Delete expired coupons and return the count of deleted rows.

tsql

CREATE PROCEDURE DeleteExpiredCoupons @DeletedCount INT OUTPUT AS BEGIN SET NOCOUNT ON; BEGIN TRANSACTION; BEGIN TRY DELETE FROM Coupons WHERE end_date < GETDATE() AND is_active = 1; SET @DeletedCount = @@ROWCOUNT; COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; RAISERROR ('Error deleting coupons: %s', 16, 1, ERROR_MESSAGE()); END CATCH; END; GO -- Execute DECLARE @Count INT; EXEC DeleteExpiredCoupons @DeletedCount = @Count OUTPUT; SELECT @Count AS DeletedCoupons;

Explanation:

• Purpose: Cleans up expired coupons.
• Details: Deletes coupons past end_date, uses transaction, returns count.
• Execution: Run to delete expired coupons.
• Output: DeletedCoupons = 2 (EASTER10, LABORDAY15).
• Use Case: Promotion maintenance.
• Challenges: Date comparisons, transaction scope.
• Best Practices: Use @@ROWCOUNT, transactions.

Question 5: Add Review (Advanced)

Scenario: Add a product review and update product rating.

tsql

CREATE PROCEDURE AddReview @ProductID INT, @CustomerID INT, @Rating INT, @Comment TEXT, @NewReviewID INT OUTPUT AS BEGIN SET NOCOUNT ON; BEGIN TRANSACTION; BEGIN TRY IF NOT EXISTS (SELECT 1 FROM Products WHERE product_id = @ProductID) BEGIN RAISERROR ('Invalid Product ID %d', 16, 1, @ProductID); RETURN; END IF NOT EXISTS (SELECT 1 FROM Customers WHERE customer_id = @CustomerID) BEGIN RAISERROR ('Invalid Customer ID %d', 16, 1, @CustomerID); RETURN; END IF @Rating NOT BETWEEN 1 AND 5 BEGIN RAISERROR ('Invalid Rating %d', 16, 1, @Rating); RETURN; END INSERT INTO Reviews (product_id, customer_id, rating, comment) VALUES (@ProductID, @CustomerID, @Rating, @Comment); SET @NewReviewID = SCOPE_IDENTITY(); UPDATE Products SET rating = (SELECT AVG(CAST(rating AS FLOAT)) FROM Reviews WHERE product_id = @ProductID) WHERE product_id = @ProductID; COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; RAISERROR ('Error adding review: %s', 16, 1, ERROR_MESSAGE()); END CATCH; END; GO -- Execute DECLARE @ReviewID INT; EXEC AddReview @ProductID = 1, @CustomerID = 2, @Rating = 4, @Comment = 'Great laptop!', @NewReviewID = @ReviewID OUTPUT; SELECT @ReviewID AS NewReviewID;

Explanation:

• Purpose: Adds a review and updates product rating.
• Details: Validates inputs, inserts review, recalculates rating.
• Execution: Run with valid inputs.
• Output: NewReviewID = 21.
• Use Case: Customer feedback system.
• Challenges: Recalculating averages, transaction scope.
• Best Practices: Use transactions, validate CHECK constraints.

Question 6: Update Seller Rating (Advanced)

Scenario: Update a seller’s rating based on their products’ reviews.

tsql

CREATE PROCEDURE UpdateSellerRating @SellerID INT, @NewRating FLOAT OUTPUT AS BEGIN SET NOCOUNT ON; BEGIN TRANSACTION; BEGIN TRY IF NOT EXISTS (SELECT 1 FROM Sellers WHERE seller_id = @SellerID) BEGIN RAISERROR ('Invalid Seller ID %d', 16, 1, @SellerID); RETURN; END UPDATE Sellers SET rating = ( SELECT AVG(CAST(r.rating AS FLOAT)) FROM Products p INNER JOIN Reviews r ON p.product_id = r.product_id WHERE p.seller_id = @SellerID ), @NewRating = ( SELECT AVG(CAST(r.rating AS FLOAT)) FROM Products p INNER JOIN Reviews r ON p.product_id = r.product_id WHERE p.seller_id = @SellerID ) WHERE seller_id = @SellerID; IF @NewRating IS NULL BEGIN SET @NewRating = 0.0; END COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; RAISERROR ('Error updating rating: %s', 16, 1, ERROR_MESSAGE()); END CATCH; END; GO -- Execute DECLARE @Rating FLOAT; EXEC UpdateSellerRating @SellerID = 1, @NewRating = @Rating OUTPUT; SELECT @Rating AS NewRating;

Explanation:

• Purpose: Updates seller rating based on reviews.
• Details: Calculates average review rating, updates Sellers.
• Execution: Run with valid @SellerID.
• Output: NewRating = 5.0 (for seller_id = 1).
• Use Case: Seller performance tracking.
• Challenges: Handling NULL averages, subquery accuracy.
• Best Practices: Use CAST for precision, transactions.

Question 7: Process Shipment (Advanced)

Scenario: Add a shipment for an order and update order status.

tsql

CREATE PROCEDURE AddShipment @OrderID INT, @Carrier VARCHAR(50), @TrackingNumber VARCHAR(50), @Cost DECIMAL(10,2), @ShippedDate DATE AS BEGIN SET NOCOUNT ON; BEGIN TRANSACTION; BEGIN TRY IF NOT EXISTS (SELECT 1 FROM Orders WHERE order_id = @OrderID) BEGIN RAISERROR ('Invalid Order ID %d', 16, 1, @OrderID); RETURN; END IF EXISTS (SELECT 1 FROM Shipments WHERE tracking_number = @TrackingNumber) BEGIN RAISERROR ('Tracking number %s already exists', 16, 1, @TrackingNumber); RETURN; END INSERT INTO Shipments (order_id, carrier, tracking_number, cost, status, shipped_date) VALUES (@OrderID, @Carrier, @TrackingNumber, @Cost, 'In Transit', @ShippedDate); UPDATE Orders SET status = 'Shipped' WHERE order_id = @OrderID; COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; RAISERROR ('Error adding shipment: %s', 16, 1, ERROR_MESSAGE()); END CATCH; END; GO -- Execute EXEC AddShipment @OrderID = 2, @Carrier = 'FedEx', @TrackingNumber = 'TRACK21', @Cost = 10.00, @ShippedDate = '2025-09-08';

Explanation:

• Purpose: Adds shipment and updates order status.
• Details: Validates order and tracking number, inserts shipment, updates status.
• Execution: Run with valid inputs.
• Output: 1 row inserted, order status updated.
• Use Case: Shipping management.
• Challenges: UNIQUE constraint on tracking_number.
• Best Practices: Use transactions, validate inputs.

Question 8: Cancel Order (Expert)

Scenario: Cancel an order, restore stock, and update payment status.

tsql

CREATE PROCEDURE CancelOrder @OrderID INT, @ErrorMessage NVARCHAR(4000) OUTPUT AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION; IF NOT EXISTS (SELECT 1 FROM Orders WHERE order_id = @OrderID) BEGIN SET @ErrorMessage = 'Invalid Order ID ' + CAST(@OrderID AS VARCHAR(10)); RAISERROR (@ErrorMessage, 16, 1); RETURN; END IF EXISTS (SELECT 1 FROM Orders WHERE order_id = @OrderID AND status = 'Delivered') BEGIN SET @ErrorMessage = 'Cannot cancel delivered order'; RAISERROR (@ErrorMessage, 16, 1); RETURN; END UPDATE Products SET stock = stock + oi.quantity FROM Products p INNER JOIN Order_Items oi ON p.product_id = oi.product_id WHERE oi.order_id = @OrderID; UPDATE Payments SET status = 'Failed' WHERE order_id = @OrderID; UPDATE Orders SET status = 'Cancelled' WHERE order_id = @OrderID; COMMIT; SET @ErrorMessage = NULL; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; SET @ErrorMessage = ERROR_MESSAGE(); RAISERROR (@ErrorMessage, 16, 1); END CATCH; END; GO -- Execute DECLARE @Error NVARCHAR(4000); EXEC CancelOrder @OrderID = 3, @ErrorMessage = @Error OUTPUT; SELECT @Error AS ErrorMessage;

Explanation:

• Purpose: Cancels an order, restores stock, updates payment.
• Details: Validates order, prevents delivered order cancellation, updates multiple tables.
• Execution: Run with valid @OrderID.
• Output: ErrorMessage = NULL, stock and status updated.
• Use Case: Order cancellation process.
• Challenges: Multi-table updates, transaction scope.
• Best Practices: Comprehensive validation, transactions.

Question 9: Update Customer Profile (Expert)

Scenario: Update customer details and address in a single transaction.

tsql

CREATE PROCEDURE UpdateCustomerProfile @CustomerID INT, @Email VARCHAR(100), @Phone VARCHAR(15), @Street VARCHAR(255), @City VARCHAR(50), @State VARCHAR(50), @ZipCode VARCHAR(10), @Country VARCHAR(50), @ErrorMessage NVARCHAR(4000) OUTPUT AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION; IF NOT EXISTS (SELECT 1 FROM Customers WHERE customer_id = @CustomerID) BEGIN SET @ErrorMessage = 'Invalid Customer ID ' + CAST(@CustomerID AS VARCHAR(10)); RAISERROR (@ErrorMessage, 16, 1); RETURN; END IF EXISTS (SELECT 1 FROM Customers WHERE email = @Email AND customer_id != @CustomerID) BEGIN SET @ErrorMessage = 'Email already in use'; RAISERROR (@ErrorMessage, 16, 1); RETURN; END UPDATE Customers SET email = @Email, phone = @Phone WHERE customer_id = @CustomerID; UPDATE Addresses SET street = @Street, city = @City, state = @State, zip_code = @ZipCode, country = @Country WHERE customer_id = @CustomerID AND type = 'Shipping'; COMMIT; SET @ErrorMessage = NULL; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; SET @ErrorMessage = ERROR_MESSAGE(); RAISERROR (@ErrorMessage, 16, 1); END CATCH; END; GO -- Execute DECLARE @Error NVARCHAR(4000); EXEC UpdateCustomerProfile @CustomerID = 1, @Email = 'updated.john@example.com', @Phone = '555-9999', @Street = '123 New St', @City = 'New York', @State = 'NY', @ZipCode = '10002', @Country = 'USA', @ErrorMessage = @Error OUTPUT; SELECT @Error AS ErrorMessage;

Explanation:

• Purpose: Updates customer profile and shipping address.
• Details: Validates customer and email, updates Customers and Addresses.
• Execution: Run with valid inputs.
• Output: ErrorMessage = NULL.
• Use Case: Profile management.
• Challenges: UNIQUE email constraint, multi-table updates.
• Best Practices: Validate inputs, use transactions.

Question 10: Process Return (Expert)

Scenario: Process a return, update stock, and mark order item as returned.

tsql

CREATE PROCEDURE ProcessReturn @OrderItemID INT, @ReturnStatus VARCHAR(20), @ErrorMessage NVARCHAR(4000) OUTPUT AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION; IF NOT EXISTS (SELECT 1 FROM Order_Items WHERE item_id = @OrderItemID) BEGIN SET @ErrorMessage = 'Invalid Order Item ID ' + CAST(@OrderItemID AS VARCHAR(10)); RAISERROR (@ErrorMessage, 16, 1); RETURN; END IF @ReturnStatus NOT IN ('Requested', 'Approved') BEGIN SET @ErrorMessage = 'Invalid Return Status ' + @ReturnStatus; RAISERROR (@ErrorMessage, 16, 1); RETURN; END UPDATE Products SET stock = stock + oi.quantity FROM Products p INNER JOIN Order_Items oi ON p.product_id = oi.product_id WHERE oi.item_id = @OrderItemID; UPDATE Order_Items SET return_status = @ReturnStatus WHERE item_id = @OrderItemID; COMMIT; SET @ErrorMessage = NULL; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; SET @ErrorMessage = ERROR_MESSAGE(); RAISERROR (@ErrorMessage, 16, 1); END CATCH; END; GO -- Execute DECLARE @Error NVARCHAR(4000); EXEC ProcessReturn @OrderItemID = 1, @ReturnStatus = 'Approved', @ErrorMessage = @Error OUTPUT; SELECT @Error AS ErrorMessage;

Explanation:

• Purpose: Processes a product return, restoring stock.
• Details: Validates item and status, updates Products and Order_Items.
• Execution: Run with valid @OrderItemID.
• Output: ErrorMessage = NULL.
• Use Case: Return processing.
• Challenges: CHECK constraint on return_status, transaction scope.
• Best Practices: Validate status, use transactions.

---

Best Practices and Standards

• Naming: Use clear names (e.g., AddOrderItem vs Proc1).
• SET NOCOUNT ON: Reduce network traffic.
• Validation: Check inputs against constraints (FK, CHECK, UNIQUE).
• Transactions: Use for multi-table updates to ensure atomicity.
• Error Handling: Implement TRY-CATCH, return meaningful @ErrorMessage.
• Output Parameters: Use for feedback (e.g., IDs, counts).
• Concurrency: Consider UPDLOCK or SERIALIZABLE for stock updates.
• Documentation: Comment code, document parameters in SSMS.
• Testing: Test edge cases (e.g., invalid IDs, zero quantities).
• Performance: Avoid cursors, use set-based operations.

---

Execution Notes

• Run ECommerceDB.sql first to create the database.
• Execute procedures in SSMS with USE ECommerceDB.
• Test with varied inputs to verify error handling.
• Use temporary tables or variables to capture outputs.
• Check sys.procedures for procedure existence.

---

Conclusion

This guide provides a thorough exploration of stored procedures, from basic queries to complex multi-table operations, using the ECommerceDB. The 10 examples and 10 assignments cover real-world e-commerce scenarios, ensuring practical learning. The progression from Primary to Expert levels builds skills systematically, with robust error handling and transactions for reliability. Trainers can use the assignments to test applicants, while developers can apply these patterns to e-commerce systems. For further exploration, consider table-valued parameters, logging errors to a table, or integrating with external APIs.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone