Flyers 2 – Flyers v1: Manual Mayhem

CREATE PROCEDURE InsertNormalizedAd
    @industry VARCHAR(50),
    @merchant VARCHAR(100),
    @brand VARCHAR(100),
    @product VARCHAR(100),
    @productdescription VARCHAR(255),
    @productcategory VARCHAR(100),
    @generalproduct VARCHAR(100),
    @uri VARCHAR(255),
    @startdate DATE,
    @enddate DATE,
    @distinction VARCHAR(50),
    @amount VARCHAR(50),
    @min INT,
    @max INT,
    @price DECIMAL(10,2)
AS
BEGIN
--check if industry exists
    DECLARE @industryid INT;
    SET @industryid = (
        SELECT IndustryID FROM DIM_Industry
        WHERE Industry = @industry
    );
    IF @industryid IS NULL
    BEGIN
    --insert into dim_industry
        INSERT INTO DIM_Industry
            VALUES (@industry);
    --set @industryid to newly created industryID 
        SET @industryid = (
            SELECT IndustryID FROM DIM_Industry
            WHERE Industry = @industry
        );
    END
--check if merchant exists
    DECLARE @merchantid INT;
    SET @merchantid = (
        SELECT MerchantID FROM DIM_Merchant
        WHERE Merchant = @merchant
    );
    IF @merchantid IS NULL
    BEGIN
    --insert into dim_merchant
        INSERT INTO DIM_Merchant
            VALUES (@merchant);
    --set @merchantid to newly created merchantID 
        SET @merchantid = (
            SELECT MerchantID FROM DIM_Merchant
            WHERE Merchant = @merchant
        );
    END
--check if brand exists
    DECLARE @brandid INT;
    SET @brandid = (
        SELECT BrandID FROM DIM_Brand
        WHERE Brand = @brand
    );
    IF @brandid IS NULL
    BEGIN
    --insert into dim_brand
        INSERT INTO DIM_Brand
            VALUES (@brand);
    --set @brandid to newly created brandID 
        SET @brandid = (
            SELECT BrandID FROM DIM_Brand
            WHERE Brand = @brand
        );
    END
--check if product exists
    DECLARE @productid INT;
    SET @productid = (
        SELECT ProductID FROM DIM_Product
        WHERE Product = @product
    );
    IF @productid IS NULL
    BEGIN
    --insert into dim_product
        INSERT INTO DIM_Product
            VALUES (@product, @productdescription, @productcategory, @generalproduct);
    --set @productid to newly created productID 
        SET @productid = (
            SELECT ProductID FROM DIM_Product
            WHERE Product = @product
        );
    END
--check if flyer exists
    DECLARE @flyerid INT;
    SET @flyerid = (
        SELECT FlyerID FROM DIM_Flyer
        WHERE URI = @uri
    );
    IF @flyerid IS NULL
    BEGIN
    --insert into dim_flyer
        INSERT INTO DIM_Flyer
            VALUES (@uri);
    --set @flyerid to newly created flyerID 
        SET @flyerid = (
            SELECT FlyerID FROM DIM_Flyer
            WHERE URI = @uri
        );
    END
--insert fact entry
    INSERT INTO FACT_Ad
        VALUES (@startdate, @enddate, @industryid, @merchantid, @brandid, @productid, @flyerid, @distinction, @amount, @min, @max, @price);
END;
GO