Simplify 12 SQL statements to 2 statements

Question

I have a SQL script which I have to simplify but I don't know how... I'm pretty new to SQL.

The statements are:

IF NOT EXISTS(SELECT * FROM permissions.role WHERE name = 'lettersOfGuarantee')
BEGIN
    INSERT INTO [permissions].[role] (name, description, assignable, editable, created_by, created_date, last_modified_by, last_modified_date)
    VALUES ('lettersOfGuarantee','Εγγυητικές Επιστολές', 1, 1,'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP);
END
GO

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'chequesInCollaterals')
              AND customer_category = 1 AND user_category = 1)
BEGIN
    INSERT INTO [permissions].[role_category_rl] (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
        SELECT id, 1, 1, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
        FROM [permissions].[role] 
        WHERE name = 'chequesInCollaterals';
END

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'lettersOfGuarantee')
                        AND customer_category = 1 AND user_category = 1)
    BEGIN
        INSERT INTO [permissions].[role_category_rl] (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 1, 1, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] 
            WHERE name = 'lettersOfGuarantee';
END
GO

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'chequesInCollaterals')
AND customer_category = 2 AND user_category = 1)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 1, 2, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'chequesInCollaterals';
END

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'lettersOfGuarantee')
AND customer_category = 2 AND user_category = 1)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 1, 2, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'lettersOfGuarantee';
END
GO

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'chequesInCollaterals')
AND customer_category = 3 AND user_category = 1)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 1, 3, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'chequesInCollaterals';
END

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'lettersOfGuarantee')
AND customer_category = 3 AND user_category = 1)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 1, 3, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'lettersOfGuarantee';
END
GO

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'chequesInCollaterals')
AND customer_category = 3 AND user_category = 2)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 2, 3, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'chequesInCollaterals';
END

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'lettersOfGuarantee')
AND customer_category = 3 AND user_category = 2)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 2, 3, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'lettersOfGuarantee';
END
GO

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'chequesInCollaterals')
AND customer_category = 4 AND user_category = 2)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 2, 4, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'chequesInCollaterals';
END

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'lettersOfGuarantee')
AND customer_category = 4 AND user_category = 2)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 2, 4, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'lettersOfGuarantee';
END
GO

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'chequesInCollaterals')
AND customer_category = 5 AND user_category = 2)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 2, 5, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'chequesInCollaterals';
END

IF NOT EXISTS(SELECT * FROM permissions.role_category_rl where role_id = (select id from permissions.role where name = 'lettersOfGuarantee')
AND customer_category = 5 AND user_category = 2)
    BEGIN
        INSERT INTO [permissions].[role_category_rl]
            (role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
            SELECT id, 2, 5, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
            FROM [permissions].[role] WHERE name = 'lettersOfGuarantee';
END
GO

Since only the user_category, customer_category values are changing we must declare a list:

DECLARE @categories table (customer_cat_id BIGINT, user_cat_id BIGINT);
INSERT INTO @categories
  VALUES (1, 1),
         (1, 2),
         (1, 3),
         (2, 4),
         (2, 5),
         (2, 6);

I don't know how i can modify the two variables dynamically.

Answer 1

You could use the table variable that you have created in your select to project the user_category and customer_category:

INSERT INTO [permissions].[role_category_rl]
(role_id, user_category, customer_category, created_by, created_date, last_modified_by, last_modified_date)
SELECT id, cat.user_cat_id, cat.customer_cat_id, 'V4.69.sql', CURRENT_TIMESTAMP, 'V4.69.sql', CURRENT_TIMESTAMP
FROM @categories cat, [permissions].[role] WHERE name = 'lettersOfGuarantee';