When restoring a database from SQL Server 2005 to SQL Server 2012 – and attempting to execute CREATE PROCEDURE statement with a MERGE keyword , the following errors appeared :
Msg 102, Level 15, State 1, Procedure MyStoredProcedure, Line 159 Incorrect syntax near 'MERGE'. Msg 156, Level 15, State 1, Procedure MyStoredProcedure, Line 231 Incorrect syntax near the keyword 'as'. Msg 102, Level 15, State 1, Procedure MyStoredProcedure, Line 389 Incorrect syntax near 'END'.
The errors were caused by the Database Compatibility level set at 90. Changing the database compatibility to 100 fixed the problem.
Why is this happening?
The first clue is in SQL Server BOL “Compatibility-level setting of 90 or lower: MERGE is not enforced as a reserved keyword. Compatibility-level setting of 100: MERGE is a fully reserved keyword. The MERGE statement is supported under both 100 and 90 compatibility levels”
This means you can use MERGE on SQL Server 2008 and higher versions, with compatibility mode at 90. Using compatibility level 90 impacts the keyword enforcement , keyword enforcement impacts parsing , but not the execution phase.
Returning back to the original problem – when I was running the CREATE PROCEDURE statement with compatibility = 90 , the parsing phase of CREATE PROCEDURE didn’t interpret the MERGE keyword as reserved, therefore throwing an error.
To check the database compatibility level , use this syntax:
SELECT compatibility_level FROM sys.databases WHERE name = 'MyDB'; To alter the database compatibility level: ALTER DATABASE [MyDB] SET COMPATIBILITY_LEVEL = 100