This PL/SQL procedure archives records older than 2 years from a source table to a historical table in batches, using BULK COLLECT and FORALL for efficiency. It deletes archived records from the source table and ensures transactional consistency.
Example Sql Server:

-- T-SQL equivalent
CREATE PROCEDURE ArchiveRecords
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @BatchSize INT = 10000;
    DECLARE @RowsAffected INT = 1;
    WHILE @RowsAffected > 0
    BEGIN
        BEGIN TRANSACTION;
        -- Insert into historical table
        INSERT INTO HistoricalRecords (RecordID, Data, RecordDate)
        SELECT TOP (@BatchSize) RecordID, Data, RecordDate
        FROM Records
        WHERE RecordDate < DATEADD(YEAR, -2, GETDATE());
        SET @RowsAffected = @@ROWCOUNT;
        -- Delete archived records
        DELETE FROM Records
        WHERE RecordID IN (
            SELECT RecordID FROM HistoricalRecords
            WHERE RecordDate < DATEADD(YEAR, -2, GETDATE())
        );
        COMMIT;
    END;
END;

Example MySql:

-- MySQL equivalent
DELIMITER //
CREATE PROCEDURE ArchiveRecords()
BEGIN
    DECLARE batch_size INT DEFAULT 10000;
    DECLARE rows_affected INT DEFAULT 1;
    WHILE rows_affected > 0 DO
        START TRANSACTION;
        -- Insert into historical table
        INSERT INTO HistoricalRecords (RecordID, Data, RecordDate)
        SELECT RecordID, Data, RecordDate
        FROM Records
        WHERE RecordDate < DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
        LIMIT batch_size;
        SET rows_affected = ROW_COUNT();
        -- Delete archived records
        DELETE FROM Records
        WHERE RecordID IN (
            SELECT RecordID FROM HistoricalRecords
            WHERE RecordDate < DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
        );
        COMMIT;
    END WHILE;
END //
DELIMITER ;

This query uses a recursive CTE (WITH RECURSIVE) to fetch an organizational hierarchy, displaying employee levels. It assumes a table with EmployeeID, Name, and ManagerID.
Example Sql Server:

-- T-SQL
WITH EmployeeHierarchy AS (
    SELECT EmployeeID, Name, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
    FROM Employees e
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, Name, ManagerID, Level
FROM EmployeeHierarchy
ORDER BY Level, Name;

Example MySql:

-- MySQL
WITH RECURSIVE EmployeeHierarchy AS (
    SELECT EmployeeID, Name, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
    FROM Employees e
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, Name, ManagerID, Level
FROM EmployeeHierarchy
ORDER BY Level, Name;

This PL/SQL block uses dynamic SQL to fetch distinct values from a specified table and column, validating inputs to prevent SQL injection. It returns results via a cursor.
Example Sql Server:

-- T-SQL
CREATE PROCEDURE GetDistinctValues
    @TableName NVARCHAR(128),
    @ColumnName NVARCHAR(128)
AS
BEGIN
    DECLARE @SQL NVARCHAR(500);
    SET @SQL = N'SELECT DISTINCT ' + QUOTENAME(@ColumnName) + 
               N' FROM ' + QUOTENAME(@TableName);
    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(@TableName) AND name = @ColumnName)
        EXEC sp_executesql @SQL;
    ELSE
        THROW 50001, 'Invalid table or column', 1;
END;
-- Test
EXEC GetDistinctValues @TableName = 'Employees', @ColumnName = 'DepartmentID';

Example MySql:

-- MySQL
DELIMITER //
CREATE PROCEDURE GetDistinctValues(IN TableName VARCHAR(128), IN ColumnName VARCHAR(128))
BEGIN
    SET @sql = CONCAT('SELECT DISTINCT ', QUOTE_IDENTIFIER(ColumnName), 
                      ' FROM ', QUOTE_IDENTIFIER(TableName));
    IF EXISTS (SELECT 1 FROM information_schema.columns 
               WHERE table_name = TableName AND column_name = ColumnName) THEN
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid table or column';
    END IF;
END //
DELIMITER ;
-- Test
CALL GetDistinctValues('Employees', 'DepartmentID');

This PL/SQL program uses BULK COLLECT to fetch records in batches and FORALL to update millions of records efficiently, minimizing context switches. It updates employee salaries based on a condition.
Example Sql Server:

-- T-SQL equivalent (using table-valued parameters)
CREATE TYPE RecordType AS TABLE (RecordID INT, Salary DECIMAL(10,2));
GO
CREATE PROCEDURE BulkUpdateRecords
AS
BEGIN
    DECLARE @Records RecordType;
    DECLARE @BatchSize INT = 10000;
    INSERT INTO @Records
    SELECT TOP (@BatchSize) RecordID, Salary * 1.1
    FROM Records
    WHERE Salary < 50000;
    UPDATE r
    SET Salary = t.Salary
    FROM Records r
    JOIN @Records t ON r.RecordID = t.RecordID;
END;

Example MySql:

-- MySQL equivalent (using temporary table)
DELIMITER //
CREATE PROCEDURE BulkUpdateRecords()
BEGIN
    DECLARE batch_size INT DEFAULT 10000;
    CREATE TEMPORARY TABLE TempRecords (RecordID INT, NewSalary DECIMAL(10,2));
    INSERT INTO TempRecords
    SELECT RecordID, Salary * 1.1
    FROM Records
    WHERE Salary < 50000
    LIMIT batch_size;
    UPDATE Records r
    JOIN TempRecords t ON r.RecordID = t.RecordID
    SET r.Salary = t.NewSalary;
    DROP TEMPORARY TABLE TempRecords;
END //
DELIMITER ;

A pipelined table function in PL/SQL returns rows incrementally, improving performance for large datasets. This function transforms employee data (e.g., categorizes salaries) and pipes rows.
Example Sql Server:

-- T-SQL (no pipelined functions, use table-valued function)
CREATE FUNCTION GetEmployeeCategories()
RETURNS @Result TABLE (EmployeeID INT, Name VARCHAR(50), SalaryCategory VARCHAR(20))
AS
BEGIN
    INSERT INTO @Result
    SELECT EmployeeID, Name,
           CASE WHEN Salary > 50000 THEN 'High' ELSE 'Low' END
    FROM Employees;
    RETURN;
END;
-- Test
SELECT * FROM dbo.GetEmployeeCategories();

Example MySql:

-- MySQL (no pipelined functions, use stored procedure)
DELIMITER //
CREATE PROCEDURE GetEmployeeCategories()
BEGIN
    SELECT EmployeeID, Name,
           CASE WHEN Salary > 50000 THEN 'High' ELSE 'Low' END AS SalaryCategory
    FROM Employees;
END //
DELIMITER ;
-- Test
CALL GetEmployeeCategories();

This PL/SQL logging mechanism uses an autonomous transaction to log errors to a table, ensuring logs persist even if the main transaction rolls back. It captures error messages, stack traces, and timestamps.
Example Sql Server:

-- T-SQL
CREATE TABLE ErrorLog (
    LogID INT IDENTITY PRIMARY KEY,
    ErrorMessage NVARCHAR(4000),
    ErrorTime DATETIME,
    ProcedureName NVARCHAR(128)
);
CREATE PROCEDURE LogError
    @ErrorMsg NVARCHAR(4000),
    @ProcName NVARCHAR(128)
AS
BEGIN
    INSERT INTO ErrorLog (ErrorMessage, ErrorTime, ProcedureName)
    VALUES (@ErrorMsg, GETDATE(), @ProcName);
END;
-- Usage
CREATE PROCEDURE ProcessData
AS
BEGIN
    BEGIN TRY
        SELECT 1/0;
    END TRY
    BEGIN CATCH
        EXEC LogError @ErrorMsg = ERROR_MESSAGE(), @ProcName = OBJECT_NAME(@@PROCID);
        THROW;
    END CATCH;
END;

Example MySql:

-- MySQL
CREATE TABLE ErrorLog (
    LogID INT AUTO_INCREMENT PRIMARY KEY,
    ErrorMessage VARCHAR(4000),
    ErrorTime DATETIME,
    ProcedureName VARCHAR(128)
);
DELIMITER //
CREATE PROCEDURE LogError(IN ErrorMsg VARCHAR(4000), IN ProcName VARCHAR(128))
BEGIN
    INSERT INTO ErrorLog (ErrorMessage, ErrorTime, ProcedureName)
    VALUES (ErrorMsg, NOW(), ProcName);
END //
CREATE PROCEDURE ProcessData()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        CALL LogError(@error_msg, 'ProcessData');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @error_msg;
    END;
    SET @error_msg = 'Division by zero';
    SELECT 1/0;
END //
DELIMITER ;

This PL/SQL procedure wraps an API call (simulated as a stored procedure), retries up to 3 times on failure, and logs each attempt to a table using an autonomous transaction.
Example Sql Server:

-- T-SQL
CREATE TABLE APILog (
    LogID INT IDENTITY PRIMARY KEY,
    Attempt INT,
    ErrorMessage NVARCHAR(4000),
    LogTime DATETIME
);
CREATE PROCEDURE CallAPIWrapper
AS
BEGIN
    DECLARE @Attempts INT = 1;
    DECLARE @MaxAttempts INT = 3;
    WHILE @Attempts <= @MaxAttempts
    BEGIN
        BEGIN TRY
            -- Simulate API call
            EXEC SimulateAPICall;
            RETURN;
        END TRY
        BEGIN CATCH
            INSERT INTO APILog (Attempt, ErrorMessage, LogTime)
            VALUES (@Attempts, ERROR_MESSAGE(), GETDATE());
            SET @Attempts += 1;
            IF @Attempts > @MaxAttempts
                THROW;
        END CATCH;
    END;
END;

Example MySql:

-- MySQL
CREATE TABLE APILog (
    LogID INT AUTO_INCREMENT PRIMARY KEY,
    Attempt INT,
    ErrorMessage VARCHAR(4000),
    LogTime DATETIME
);
DELIMITER //
CREATE PROCEDURE CallAPIWrapper()
BEGIN
    DECLARE attempts INT DEFAULT 1;
    DECLARE max_attempts INT DEFAULT 3;
    WHILE attempts <= max_attempts DO
        BEGIN
            DECLARE EXIT HANDLER FOR SQLEXCEPTION
            BEGIN
                INSERT INTO APILog (Attempt, ErrorMessage, LogTime)
                VALUES (attempts, @error_msg, NOW());
                SET attempts = attempts + 1;
                IF attempts > max_attempts THEN
                    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @error_msg;
                END IF;
            END;
            SET @error_msg = 'API call failed';
            CALL SimulateAPICall();
            LEAVE;
        END;
    END WHILE;
END //
DELIMITER ;

This query identifies missing dates in a time series table and generates a complete date sequence using a recursive CTE or a numbers table, then LEFT JOINs to find gaps.
Example Sql Server:

-- Generate date sequence and find missing dates
WITH DateSequence AS (
    SELECT CAST('2025-01-01' AS DATE) AS RecordDate
    UNION ALL
    SELECT DATEADD(DAY, 1, RecordDate)
    FROM DateSequence
    WHERE RecordDate < '2025-12-31'
)
SELECT d.RecordDate
FROM DateSequence d
LEFT JOIN TimeSeries t ON d.RecordDate = t.RecordDate
WHERE t.RecordDate IS NULL
OPTION (MAXRECURSION 365);

Example MySql:

-- MySQL
WITH RECURSIVE DateSequence AS (
    SELECT '2025-01-01' AS RecordDate
    UNION ALL
    SELECT DATE_ADD(RecordDate, INTERVAL 1 DAY)
    FROM DateSequence
    WHERE RecordDate < '2025-12-31'
)
SELECT d.RecordDate
FROM DateSequence d
LEFT JOIN TimeSeries t ON d.RecordDate = t.RecordDate
WHERE t.RecordDate IS NULL;

This PL/SQL block inserts data into multiple related tables (e.g., Orders and OrderDetails) within a single transaction to ensure consistency, rolling back on errors.
Example Sql Server:

-- T-SQL
BEGIN TRY
    BEGIN TRANSACTION;
    DECLARE @OrderID INT;
    INSERT INTO Orders (CustomerID, OrderDate)
    VALUES (101, GETDATE());
    SET @OrderID = SCOPE_IDENTITY();
    INSERT INTO OrderDetails (OrderID, ProductID, Quantity)
    VALUES (@OrderID, 501, 10);
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT ERROR_MESSAGE();
END CATCH;

Example MySql:

-- MySQL
DELIMITER //
CREATE PROCEDURE InsertOrder()
BEGIN
    DECLARE order_id INT;
    START TRANSACTION;
    INSERT INTO Orders (CustomerID, OrderDate)
    VALUES (101, NOW());
    SET order_id = LAST_INSERT_ID();
    INSERT INTO OrderDetails (OrderID, ProductID, Quantity)
    VALUES (order_id, 501, 10);
    COMMIT;
END //
DELIMITER ;
-- Test
CALL InsertOrder();

This INSTEAD OF trigger on a complex view (joining Employees and Departments) handles INSERT and UPDATE operations by redirecting them to the underlying tables. MySQL does not support INSTEAD OF triggers, so a stored procedure is used.
Example Sql Server:

-- View
CREATE VIEW EmployeeDepartment AS
SELECT e.EmployeeID, e.Name, e.Salary, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;
-- INSTEAD OF INSERT trigger
CREATE TRIGGER trg_InsteadOfInsert
ON EmployeeDepartment
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO Employees (EmployeeID, Name, Salary, DepartmentID)
    SELECT i.EmployeeID, i.Name, i.Salary, d.DepartmentID
    FROM inserted i
    JOIN Departments d ON i.DepartmentName = d.DepartmentName;
END;
-- INSTEAD OF UPDATE trigger
CREATE TRIGGER trg_InsteadOfUpdate
ON EmployeeDepartment
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE e
    SET Name = i.Name, Salary = i.Salary, DepartmentID = d.DepartmentID
    FROM Employees e
    JOIN inserted i ON e.EmployeeID = i.EmployeeID
    JOIN Departments d ON i.DepartmentName = d.DepartmentName;
END;

Example MySql:

-- MySQL (use stored procedure)
CREATE VIEW EmployeeDepartment AS
SELECT e.EmployeeID, e.Name, e.Salary, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;
DELIMITER //
CREATE PROCEDURE InsertUpdateEmployeeDepartment(
    IN p_EmployeeID INT, IN p_Name VARCHAR(50),
    IN p_Salary DECIMAL(10,2), IN p_DepartmentName VARCHAR(50),
    IN p_Operation VARCHAR(10)
)
BEGIN
    IF p_Operation = 'INSERT' THEN
        INSERT INTO Employees (EmployeeID, Name, Salary, DepartmentID)
        SELECT p_EmployeeID, p_Name, p_Salary, DepartmentID
        FROM Departments WHERE DepartmentName = p_DepartmentName;
    ELSEIF p_Operation = 'UPDATE' THEN
        UPDATE Employees e
        JOIN Departments d ON d.DepartmentName = p_DepartmentName
        SET e.Name = p_Name, e.Salary = p_Salary, e.DepartmentID = d.DepartmentID
        WHERE e.EmployeeID = p_EmployeeID;
    END IF;
END //
DELIMITER ;
-- Test
CALL InsertUpdateEmployeeDepartment(1, 'John', 50000, 'HR', 'INSERT');

This query flattens a JSON column using JSON functions to extract nested fields (e.g., name and address from a JSON object). SQL Server uses OPENJSON; MySQL uses JSON_EXTRACT.
Example Sql Server:

-- SQL Server
SELECT 
    t.ID,
    j.Name,
    j.Address
FROM DataTable t
CROSS APPLY OPENJSON(t.JsonColumn) 
WITH (
    Name NVARCHAR(50) '$.name',
    Address NVARCHAR(100) '$.address'
) j;

Example MySql:

-- MySQL
SELECT 
    ID,
    JSON_EXTRACT(JsonColumn, '$.name') AS Name,
    JSON_EXTRACT(JsonColumn, '$.address') AS Address
FROM DataTable;

This PL/SQL procedure splits a CSV string into rows using a loop or SUBSTR/INSTR for parsing. It returns the results via a pipelined function or temporary table.
Example Sql Server:

-- T-SQL
CREATE PROCEDURE SplitCSV
    @CSV VARCHAR(8000)
AS
BEGIN
    DECLARE @Pos INT, @NextPos INT, @Value VARCHAR(100);
    SET @CSV = @CSV + ',';
    SET @Pos = 1;
    WHILE CHARINDEX(',', @CSV, @Pos) > 0
    BEGIN
        SET @NextPos = CHARINDEX(',', @CSV, @Pos);
        SET @Value = SUBSTRING(@CSV, @Pos, @NextPos - @Pos);
        SELECT TRIM(@Value) AS Value;
        SET @Pos = @NextPos + 1;
    END;
END;
-- Test
EXEC SplitCSV @CSV = 'apple,banana,orange';

Example MySql:

-- MySQL
DELIMITER //
CREATE PROCEDURE SplitCSV(IN CSV TEXT)
BEGIN
    DECLARE pos INT DEFAULT 1;
    DECLARE next_pos INT;
    DECLARE value VARCHAR(100);
    CREATE TEMPORARY TABLE TempValues (Value VARCHAR(100));
    WHILE LOCATE(',', CSV, pos) > 0 DO
        SET next_pos = LOCATE(',', CSV, pos);
        SET value = SUBSTRING(CSV, pos, next_pos - pos);
        INSERT INTO TempValues VALUES (TRIM(value));
        SET pos = next_pos + 1;
    END WHILE;
    IF pos <= LENGTH(CSV) THEN
        SET value = SUBSTRING(CSV, pos);
        INSERT INTO TempValues VALUES (TRIM(value));
    END IF;
    SELECT Value FROM TempValues;
    DROP TEMPORARY TABLE TempValues;
END //
DELIMITER ;
-- Test
CALL SplitCSV('apple,banana,orange');

This PL/SQL block uses a cursor to process rows, committing every 1,000 rows to balance performance and rollback safety, minimizing undo log growth.
Example Sql Server:

-- T-SQL
CREATE PROCEDURE ProcessRecords
AS
BEGIN
    DECLARE @RecordID INT, @Data VARCHAR(100);
    DECLARE @Counter INT = 0;
    DECLARE record_cursor CURSOR FOR
        SELECT RecordID, Data FROM Records WHERE Processed = 0;
    OPEN record_cursor;
    FETCH NEXT FROM record_cursor INTO @RecordID, @Data;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE Records SET Processed = 1 WHERE RecordID = @RecordID;
        SET @Counter += 1;
        IF @Counter >= 1000
        BEGIN
            COMMIT;
            SET @Counter = 0;
        END;
        FETCH NEXT FROM record_cursor INTO @RecordID, @Data;
    END;
    IF @Counter > 0 COMMIT;
    CLOSE record_cursor;
    DEALLOCATE record_cursor;
END;

Example MySql:

-- MySQL
DELIMITER //
CREATE PROCEDURE ProcessRecords()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE record_id INT;
    DECLARE data_val VARCHAR(100);
    DECLARE counter INT DEFAULT 0;
    DECLARE record_cursor CURSOR FOR
        SELECT RecordID, Data FROM Records WHERE Processed = 0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    START TRANSACTION;
    OPEN record_cursor;
    read_loop: LOOP
        FETCH record_cursor INTO record_id, data_val;
        IF done THEN
            LEAVE read_loop;
        END IF;
        UPDATE Records SET Processed = 1 WHERE RecordID = record_id;
        SET counter = counter + 1;
        IF counter >= 1000 THEN
            COMMIT;
            START TRANSACTION;
            SET counter = 0;
        END IF;
    END LOOP;
    IF counter > 0 THEN
        COMMIT;
    END IF;
    CLOSE record_cursor;
END //
DELIMITER ;

This PL/SQL procedure generates a CREATE TABLE DDL statement based on metadata from a schema (e.g., information_schema.columns), using dynamic SQL.
Example Sql Server:

-- T-SQL
CREATE PROCEDURE GenerateDDL
    @TableName NVARCHAR(128)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = 'CREATE TABLE ' + QUOTENAME(@TableName) + ' (';
    SELECT @SQL += '
    ' + QUOTENAME(name) + ' ' + 
                   CASE WHEN system_type_id = 56 THEN 'INT'
                        WHEN system_type_id = 167 THEN 'VARCHAR(' + CAST(max_length AS NVARCHAR) + ')'
                        ELSE 'UNKNOWN' END + ','
    FROM sys.columns
    WHERE object_id = OBJECT_ID(@TableName);
    SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + '
);';
    PRINT @SQL;
END;
-- Test
EXEC GenerateDDL @TableName = 'Employees';

Example MySql:

-- MySQL
DELIMITER //
CREATE PROCEDURE GenerateDDL(IN TableName VARCHAR(128))
BEGIN
    SET @sql = CONCAT('CREATE TABLE ', QUOTE_IDENTIFIER(TableName), ' (');
    SELECT GROUP_CONCAT(
        CONCAT(QUOTE_IDENTIFIER(column_name), ' ', 
               CASE WHEN data_type = 'int' THEN 'INT'
                    WHEN data_type = 'varchar' THEN CONCAT('VARCHAR(', character_maximum_length, ')')
                    ELSE 'UNKNOWN' END)
        SEPARATOR ', '
    ) INTO @columns
    FROM information_schema.columns
    WHERE table_name = TableName;
    SET @sql = CONCAT(@sql, '
    ', @columns, '
);');
    SELECT @sql;
END //
DELIMITER ;
-- Test
CALL GenerateDDL('Employees');

This PL/SQL procedure rotates partitions for an audit table by creating a new partition for the next month, moving old data to an archive table, and dropping old partitions.
Example Sql Server:

-- T-SQL (no native partitioning, use manual table rotation)
CREATE PROCEDURE RotateAuditTable
AS
BEGIN
    DECLARE @NewTableName NVARCHAR(128) = 'Audit_' + FORMAT(GETDATE(), 'yyyyMM');
    DECLARE @SQL NVARCHAR(1000);
    SET @SQL = 'CREATE TABLE ' + @NewTableName + ' (
        AuditID INT PRIMARY KEY,
        Event VARCHAR(100),
        EventDate DATETIME
    );';
    EXEC sp_executesql @SQL;
    -- Archive old data
    INSERT INTO Audit_Archive
    SELECT * FROM Audit
    WHERE EventDate < DATEADD(MONTH, -24, GETDATE());
    DELETE FROM Audit WHERE EventDate < DATEADD(MONTH, -24, GETDATE());
END;

Example MySql:

-- MySQL
DELIMITER //
CREATE PROCEDURE RotateAuditTable()
BEGIN
    DECLARE new_table_name VARCHAR(128);
    SET new_table_name = CONCAT('Audit_', DATE_FORMAT(NOW(), '%Y%m'));
    SET @sql = CONCAT('CREATE TABLE ', new_table_name, ' (
        AuditID INT PRIMARY KEY,
        Event VARCHAR(100),
        EventDate DATETIME
    );');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    -- Archive old data
    INSERT INTO Audit_Archive
    SELECT * FROM Audit
    WHERE EventDate < DATE_SUB(NOW(), INTERVAL 24 MONTH);
    DELETE FROM Audit WHERE EventDate < DATE_SUB(NOW(), INTERVAL 24 MONTH);
END //
DELIMITER ;

This query detects overlapping date ranges in a bookings table by comparing each booking’s start and end dates with others, using a self-join.
Example Sql Server:

-- SQL Server
SELECT b1.BookingID AS Booking1, b2.BookingID AS Booking2,
       b1.StartDate, b1.EndDate, b2.StartDate, b2.EndDate
FROM Bookings b1
JOIN Bookings b2 ON b1.BookingID < b2.BookingID
WHERE b1.StartDate <= b2.EndDate AND b1.EndDate >= b2.StartDate;

Example MySql:

-- MySQL
SELECT b1.BookingID AS Booking1, b2.BookingID AS Booking2,
       b1.StartDate, b1.EndDate, b2.StartDate, b2.EndDate
FROM Bookings b1
JOIN Bookings b2 ON b1.BookingID < b2.BookingID
WHERE b1.StartDate <= b2.EndDate AND b1.EndDate >= b2.StartDate;

This stored procedure syncs data between a source and target table, using a MERGE statement to handle inserts, updates, and deletes, and logs changes to a history table.
Example Sql Server:

-- T-SQL
CREATE TABLE ChangeLog (
    LogID INT IDENTITY PRIMARY KEY,
    RecordID INT,
    Operation VARCHAR(10),
    ChangeTime DATETIME
);
CREATE PROCEDURE SyncTables
AS
BEGIN
    MERGE INTO TargetTable t
    USING SourceTable s
    ON t.RecordID = s.RecordID
    WHEN MATCHED AND s.Data <> t.Data THEN
        UPDATE SET Data = s.Data
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (RecordID, Data)
        VALUES (s.RecordID, s.Data)
    WHEN NOT MATCHED BY SOURCE THEN
        DELETE
    OUTPUT deleted.RecordID, $action, GETDATE() INTO ChangeLog (RecordID, Operation, ChangeTime);
END;

Example MySql:

-- MySQL (no MERGE, use INSERT/UPDATE/DELETE)
CREATE TABLE ChangeLog (
    LogID INT AUTO_INCREMENT PRIMARY KEY,
    RecordID INT,
    Operation VARCHAR(10),
    ChangeTime DATETIME
);
DELIMITER //
CREATE PROCEDURE SyncTables()
BEGIN
    -- Insert new records
    INSERT INTO TargetTable (RecordID, Data)
    SELECT s.RecordID, s.Data
    FROM SourceTable s
    LEFT JOIN TargetTable t ON s.RecordID = t.RecordID
    WHERE t.RecordID IS NULL;
    INSERT INTO ChangeLog (RecordID, Operation, ChangeTime)
    SELECT s.RecordID, 'INSERT', NOW()
    FROM SourceTable s
    LEFT JOIN TargetTable t ON s.RecordID = t.RecordID
    WHERE t.RecordID IS NULL;
    -- Update changed records
    UPDATE TargetTable t
    JOIN SourceTable s ON t.RecordID = s.RecordID
    SET t.Data = s.Data
    WHERE t.Data <> s.Data;
    INSERT INTO ChangeLog (RecordID, Operation, ChangeTime)
    SELECT t.RecordID, 'UPDATE', NOW()
    FROM TargetTable t
    JOIN SourceTable s ON t.RecordID = s.RecordID
    WHERE t.Data <> s.Data;
    -- Delete missing records
    DELETE FROM TargetTable WHERE RecordID NOT IN (SELECT RecordID FROM SourceTable);
    INSERT INTO ChangeLog (RecordID, Operation, ChangeTime)
    SELECT RecordID, 'DELETE', NOW()
    FROM TargetTable WHERE RecordID NOT IN (SELECT RecordID FROM SourceTable);
END //
DELIMITER ;

This MERGE statement syncs a target table with a source table, inserting new records, updating existing ones if data changes, and deleting records not in the source, with business logic (e.g., only update if salary increased).
Example Sql Server:

-- SQL Server
MERGE INTO EmployeesTarget t
USING EmployeesSource s
ON t.EmployeeID = s.EmployeeID
WHEN MATCHED AND s.Salary > t.Salary THEN
    UPDATE SET Name = s.Name, Salary = s.Salary
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, Name, Salary)
    VALUES (s.EmployeeID, s.Name, s.Salary)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, deleted.*, inserted.*;

Example MySql:

-- MySQL (no MERGE, simulate with INSERT/UPDATE/DELETE)
INSERT INTO EmployeesTarget (EmployeeID, Name, Salary)
SELECT s.EmployeeID, s.Name, s.Salary
FROM EmployeesSource s
LEFT JOIN EmployeesTarget t ON s.EmployeeID = t.EmployeeID
WHERE t.EmployeeID IS NULL;
UPDATE EmployeesTarget t
JOIN EmployeesSource s ON t.EmployeeID = s.EmployeeID
SET t.Name = s.Name, t.Salary = s.Salary
WHERE s.Salary > t.Salary;
DELETE FROM EmployeesTarget WHERE EmployeeID NOT IN (SELECT EmployeeID FROM EmployeesSource);

This PL/SQL procedure enforces row-level access by joining a user roles table with the data table, filtering rows based on the user’s role. It uses a parameterized query to ensure security.
Example Sql Server:

-- T-SQL
CREATE TABLE UserRoles (
    UserID INT,
    Role VARCHAR(50),
    DepartmentID INT
);
CREATE PROCEDURE GetEmployeeData
    @UserID INT
AS
BEGIN
    SELECT e.EmployeeID, e.Name, e.Salary
    FROM Employees e
    JOIN UserRoles ur ON e.DepartmentID = ur.DepartmentID
    WHERE ur.UserID = @UserID
    AND ur.Role = 'Manager';
END;
-- Test
EXEC GetEmployeeData @UserID = 1;

Example MySql:

-- MySQL
CREATE TABLE UserRoles (
    UserID INT,
    Role VARCHAR(50),
    DepartmentID INT
);
DELIMITER //
CREATE PROCEDURE GetEmployeeData(IN UserID INT)
BEGIN
    SELECT e.EmployeeID, e.Name, e.Salary
    FROM Employees e
    JOIN UserRoles ur ON e.DepartmentID = ur.DepartmentID
    WHERE ur.UserID = UserID
    AND ur.Role = 'Manager';
END //
DELIMITER ;
-- Test
CALL GetEmployeeData(1);

This query compares the structure of two tables by querying system catalogs (sys.columns for SQL Server, information_schema.columns for MySQL), identifying differences in columns and data types.
Example Sql Server:

-- SQL Server
SELECT 
    COALESCE(t1.name, t2.name) AS ColumnName,
    t1.type_name AS Table1_DataType,
    t2.type_name AS Table2_DataType,
    CASE WHEN t1.name IS NULL THEN 'Missing in Table1'
         WHEN t2.name IS NULL THEN 'Missing in Table2'
         WHEN t1.type_name <> t2.type_name THEN 'Type Mismatch'
         ELSE 'Match' END AS Status
FROM (
    SELECT c.name, t.name AS type_name
    FROM sys.columns c
    JOIN sys.types t ON c.user_type_id = t.user_type_id
    WHERE c.object_id = OBJECT_ID('Table1')
) t1
FULL OUTER JOIN (
    SELECT c.name, t.name AS type_name
    FROM sys.columns c
    JOIN sys.types t ON c.user_type_id = t.user_type_id
    WHERE c.object_id = OBJECT_ID('Table2')
) t2 ON t1.name = t2.name;

Example MySql:

-- MySQL
SELECT 
    COALESCE(t1.column_name, t2.column_name) AS ColumnName,
    t1.data_type AS Table1_DataType,
    t2.data_type AS Table2_DataType,
    CASE WHEN t1.column_name IS NULL THEN 'Missing in Table1'
         WHEN t2.column_name IS NULL THEN 'Missing in Table2'
         WHEN t1.data_type <> t2.data_type THEN 'Type Mismatch'
         ELSE 'Match' END AS Status
FROM (
    SELECT column_name, data_type
    FROM information_schema.columns
    WHERE table_name = 'Table1'
) t1
FULL OUTER JOIN (
    SELECT column_name, data_type
    FROM information_schema.columns
    WHERE table_name = 'Table2'
) t2 ON t1.column_name = t2.column_name;