CODEWITHSUNDEEP
sql-serverazure-sql-database
Hand-E-Food
Hand-E-Food

Reputation: 12794

Write a script to run on different SQL engines

I'm writing a SQL script that needs to be able to run on multiple SQL engines, namely SQL Server and Azure SQL. An IF statement checks the database version to execute the correct code. But the code won't compile if used on the wrong engine.

This works fine in Azure SQL, but SQL Server rejects it.

IF (@@VERSION LIKE 'Microsoft SQL Azure %')
BEGIN
    ADD SENSITIVITY CLASSIFICATION TO [dbo].[User].[UserName] WITH (LABEL = 'Confidential', INFORMATION_TYPE = 'Credentials')
    ...
END
GO

What are my options for ensuring this is accepted by both engines?

Upvotes: 1

Views: 75

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

Dynamic SQL could be used to circumvent syntax check:

IF (@@VERSION LIKE 'Microsoft SQL Azure %')
BEGIN
    EXEC('ADD SENSITIVITY CLASSIFICATION TO [dbo].[User].[UserName] WITH (LABEL = ''Confidential'', INFORMATION_TYPE = ''Credentials'')');
    ...
END
GO

Please note that all ' have to be quoted i.e.''. Add support for here-strings i T-SQL

ADD SENSITIVITY CLASSIFICATION will be supported starting from SQL Server 2019.

Upvotes: 3

Related Questions