Write a script to run on different SQL engines

Question

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?

Lukasz Szozda · Accepted Answer · 2019-08-15 07:22:36Z

3

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.

edited Aug 15, 2019 at 7:22

answered Aug 15, 2019 at 7:16

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Write a script to run on different SQL engines

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related