Robust SQL Data Processing with TRY/CATCH

Data processing often involves unexpected issues like invalid data types, missing records, or constraint violations. Implementing robust error handling using TRY/CATCH blocks in SQL is crucial for preventing script failures and ensuring data integrity. This challenge will test your ability to gracefully handle potential errors during data manipulation operations.

Problem Description

You are tasked with writing SQL code that attempts to insert data into a table named Products. The Products table has the following structure:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    StockQuantity INT NOT NULL
);

Your code should attempt to insert a series of product records. However, some of these records may contain errors that will cause the INSERT statement to fail. These errors could include:

• Duplicate Primary Key: Attempting to insert a product with a ProductID that already exists.
• Data Type Mismatch: Providing a value of the wrong data type for a column (e.g., a string for Price).
• Constraint Violation: Violating a NOT NULL constraint by providing a NULL value for a required column.
• Other Errors: General SQL errors that might occur during the insertion process.

Your code must use a TRY/CATCH block to handle these errors. Inside the CATCH block, you should log the error message and the ProductID that caused the error. The logging should be done by inserting the error information into a separate table called ErrorLog. The ErrorLog table has the following structure:

CREATE TABLE ErrorLog (
    ErrorID INT IDENTITY(1,1) PRIMARY KEY,
    ProductID INT,
    ErrorMessage VARCHAR(255) NOT NULL,
    ErrorTimestamp DATETIME DEFAULT GETDATE()
);

The INSERT statements should continue to execute even if an error occurs. The goal is to process as many records as possible and log any errors encountered along the way.

Examples

Example 1:

-- Input: A set of product records, one of which has a duplicate ProductID.
-- Records: (1, 'Laptop', 1200.00, 10), (2, 'Mouse', 25.00, 50), (1, 'Keyboard', 75.00, 25)

-- Expected Output:
-- The first two records (ProductID 1 and 2) are inserted into the Products table.
-- The third record (ProductID 1) is not inserted into the Products table.
-- An error record is inserted into the ErrorLog table with ProductID 1 and the appropriate error message (e.g., "Violation of PRIMARY KEY constraint 'PK_Products'").

-- Explanation: The TRY block attempts to insert all records. When the duplicate ProductID is encountered, the CATCH block is executed, logging the error and ProductID. The script continues to the next record.

Example 2:

-- Input: A set of product records, one of which has a NULL ProductName.
-- Records: (3, 'Monitor', 300.00, 20), (4, 'Webcam', 50.00, NULL)

-- Expected Output:
-- The first record (ProductID 3) is inserted into the Products table.
-- The second record (ProductID 4) is not inserted into the Products table.
-- An error record is inserted into the ErrorLog table with ProductID 4 and the appropriate error message (e.g., "Cannot insert NULL value into column 'ProductName', table 'dbo.Products', column is not allowed to be NULL.").

-- Explanation: The TRY block attempts to insert all records. When the NULL ProductName is encountered, the CATCH block is executed, logging the error and ProductID.

Example 3: (Edge Case - Invalid Data Type)

-- Input: A set of product records, one of which has a string value for Price.
-- Records: (5, 'Speaker', '100.00', 30)

-- Expected Output:
-- No records are inserted.
-- An error record is inserted into the ErrorLog table with ProductID 5 and the appropriate error message (e.g., "Conversion failed when converting the varchar value '100.00' to data type decimal.").

-- Explanation: The TRY block attempts to insert all records. When the invalid Price is encountered, the CATCH block is executed, logging the error and ProductID.

Constraints

• The SQL dialect should be compatible with Microsoft SQL Server.
• The ErrorLog table must be populated with the ProductID and the error message.
• The script must handle all potential errors described in the Problem Description.
• The script should be efficient and avoid unnecessary operations.
• The script must be able to process at least 100 records without significant performance degradation.

Notes

• The specific error messages in the ErrorLog table may vary depending on the SQL Server version and configuration. Focus on capturing the ProductID and a meaningful error description.
• Consider using ERROR_MESSAGE() and ERROR_NUMBER() functions within the CATCH block to retrieve detailed error information.
• The ProductID is crucial for identifying the problematic record. Ensure it's consistently captured in the ErrorLog.
• Think about how to handle multiple errors within a single TRY block. The CATCH block should process all errors that occur within the TRY block.
• Pseudocode is acceptable for demonstrating the logic. Focus on the structure of the TRY/CATCH block and how errors are handled. You do not need to provide a fully executable SQL script.
• Assume the Products and ErrorLog tables already exist.

Pseudocode:

BEGIN TRANSACTION; -- Optional, but good practice for atomicity

BEGIN TRY
    -- Loop through a list of product records (e.g., from a temporary table or a variable)
    FOR EACH product_record IN product_records DO
        -- Attempt to insert the product record into the Products table
        INSERT INTO Products (ProductID, ProductName, Price, StockQuantity)
        VALUES (product_record.ProductID, product_record.ProductName, product_record.Price, product_record.StockQuantity);
    END FOR
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- If an error occurs, log the error information into the ErrorLog table
    DECLARE @ErrorMessage VARCHAR(255);
    SET @ErrorMessage = ERROR_MESSAGE();

    INSERT INTO ErrorLog (ProductID, ErrorMessage)
    VALUES (product_record.ProductID, @ErrorMessage); -- Use the ProductID from the last attempted record

    -- Optionally, re-throw the error or log additional information
    -- THROW; -- Re-throw the error to halt execution (optional)
    -- PRINT 'Error occurred. See ErrorLog for details.';
END CATCH;