Execute SQL conditionally based on database Compatibility Level 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 &gt;= 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?
Final Solution
I went for the dynamic SQL solution, as it &quot;just works&quot;, 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 &gt;= 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 &lt; 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 &gt;= 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 &quot;magic string&quot; 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.