T-SQL : Alter Database statement inside IF is failing-CodePudding

We have a scenario where we have to change database compatibility based on SQL Server version and we have written below condition for the same.

IF @@VERSION LIKE 'Microsoft SQL Server 2016 %' 
BEGIN
    ALTER DATABASE [AdventureWorks2014] 
        SET COMPATIBILITY_LEVEL = 130
END
GO

In one of our customer's server this code is failing with below error and the server has SQL Server 2014

Msg 15048, Level 16, State 1, Line 4
Valid values of the database compatibility level are 100, 110, or 120.

i.e. even though the condition is false, the Alter Database statement is still executing.

Tested the same condition with below code and the condition worked, it did not returned NULL as the server has SQL Server 2014 instead of 2016

IF @@VERSION LIKE 'Microsoft SQL Server 2016 %' 
BEGIN
    SELECT NULL
END
GO

So, why is the Alter Database statement being executed all the time even if the condition is false?

I believe, ideally it should not even enter OR pass the IF condition as it is checking for SQL Server 2016 but in actual the script is running on SQL Server 2014.

CodePudding user response：

This is parsing error; SQL server is parsing you want level 130 and erroring before it even runs the statements. I can replicate the issue on an older server with the following:

CREATE DATABASE testDB;
GO

IF 1=0
    ALTER DATABASE testDB SET COMPATIBILITY_LEVEL = 130;
GO

DROP DATABASE testDB;

Note that the ALTER DATABASE statement could never be run, yet this will produce the same error.

Instead, defer the validation of the statement by running it in a separate scope, if needed:

CREATE DATABASE testDB;
GO

IF @@VERSION LIKE 'Microsoft SQL Server 2016 %'
    EXEC sys.sp_executesql N'ALTER DATABASE testDB SET COMPATIBILITY_LEVEL = 130;';
GO

DROP DATABASE testDB;