Execute SQL conditionally based on database Compatibility Level

Question

The Problem

I have a script that I need to execute on both SQL Server 2016 and SQL Server 2019. If executing on Server 2019+, I want to use syntax that is only supported on that version (specifically, Data Classification).

What I want is something like this, where we simply skip Data Classification if running on a version before 2019:

DECLARE @compatibility_level TINYINT = 0;
SELECT @compatibility_level = compatibility_level FROM sys.databases WHERE name = 'MyDatabase';
PRINT CONCAT('Database compatibility level is: ', @compatibility_level);

IF @compatibility_level >= 150
BEGIN
    ADD SENSITIVITY CLASSIFICATION TO
        MyTable.Forename,
        MyTable.Surname
        WITH (LABEL='Confidential - GDPR', INFORMATION_TYPE='Name', RANK=MEDIUM);

    -- More classifications would be here
END
ELSE
BEGIN
    PRINT 'Data Classification will not be performed, as the compatibility level is not high enough';
END

If I run this on SQL Server 2019+, it works as expected. But if I run it on older version, it fails with Incorrect syntax near 'SENSITIVITY', because it's parsing the whole script before executing it.

Restrictions

I should add that this is part of a set of database migration scripts that are being executed automatically, so I can't simply execute different scripts for different versions (not without a lot of work).

Potential Solutions

One solution I thought of is to build the SQL dynamically, inside the IF block. Something like:

DECLARE @sql NVARCHAR(MAX) = '';
@sql += 'ADD SENSITIVITY CLASSIFICATION TO MyTable1.Column1 ... ;';
@sql += 'ADD SENSITIVITY CLASSIFICATION TO MyTable2.Column1 ... ;';
EXEC sp_executesql @sql;

I haven't tried this, but presume it would work. It feels pretty ugly though - are there any other ways to achieve this in a single script?

Cocowalla · Accepted Answer · 2021-02-08 12:08:29Z

Final Solution

I went for the dynamic SQL solution, as it "just works", and it's the most obvious:

DECLARE @compatibility_level TINYINT = 0;
SELECT @compatibility_level = compatibility_level FROM sys.databases WHERE name = 'MyDatabase';
PRINT CONCAT('Database compatibility level is: ', @compatibility_level);

-- Data Classification is only supported from SQL Server 2019 onwards, so we skip it if not supported
IF @compatibility_level >= 150
BEGIN
    DECLARE @sql NVARCHAR(MAX) = '';

    SELECT @sql += 'ADD SENSITIVITY CLASSIFICATION TO 
        MySchema.MyTable1.Column1, 
        MySchema.MyTable1.Column2
        WITH (LABEL=''Confidential - GDPR'', INFORMATION_TYPE=''Name'', RANK=MEDIUM);' + CHAR(10);

    SELECT @sql += 'ADD SENSITIVITY CLASSIFICATION TO 
        MySchema.MyTable2.Column1
        WITH (LABEL=''Confidential'', INFORMATION_TYPE=''Contact Info'', RANK=MEDIUM);' + CHAR(10);
        PRINT @sql;

    -- ...more classifications here, elided

    PRINT @sql;
    EXEC sp_executesql @sql;
END
ELSE
BEGIN
    RAISERROR('Data Classification will not be performed, as database compatibility level %d is not high enough', 10, 1, @compatibility_level);
END;

Other Potential Solutions

I thought to use NOEXEC, to prevent executing SQL if the database compatibility level was too low:

DECLARE @compatibility_level TINYINT = 0;
SELECT @compatibility_level = compatibility_level FROM sys.databases WHERE name = 'MyDatabase';
PRINT CONCAT('Database compatibility level is: ', @compatibility_level);

IF @compatibility_level < 150
BEGIN
    RAISERROR('Data Classification will not be performed, as database compatibility level %d is not high enough', 10, 1, @compatibility_level);
      SET NOEXEC ON;
END;
GO

-- This will only be executed if database compatibility is >= 150
ADD SENSITIVITY CLASSIFICATION TO
    MyTable.Forename,
    MyTable.Surname
    WITH (LABEL='Confidential - GDPR', INFORMATION_TYPE='Name', RANK=MEDIUM);

SET NOEXEC OFF;
GO

However, even with NOEXEC enabled, it still actually interprets the SQL - so this doesn't work if the server compatiblity level is too low (beforew SQL2019), regardless of the database compatiblity level.

@martin-cairney came up with another solution, using the sp_addextendedproperty function, and you can find an example here. I personally find having to mess around with "magic string" GUIDs for sys_sensitivity_label_id to be a PITA.
A further option would be to write directly to the sys.extended_properties table (this is exactly what sp_addextendedproperty does) - obviously this method isn't recommended.

You might also consider checking for the associated system objects to avoid needing to hard code any version-related checks. I implement this exact approach in a project, for example. It is a more future-proof approach (in the past MS has backported features to older versions, for example). — LowlyDBA - John M
– LowlyDBA - John M, Commented Feb 8, 2021 at 16:14

Stack Exchange Network

Execute SQL conditionally based on database Compatibility Level

1 Answer 1

Your Answer

Hot Network Questions

Execute SQL conditionally based on database Compatibility Level

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions