Set COMPATIBILITY LEVEL by passing value as local variable in T-SQL

Question

I am trying to get this code snipp working.

Targeting SQL 2005 SP2 and newer SQL Server products.

Code:

BEGIN
DECLARE @level tinyint;
DECLARE @levelvar tinyint;
IF substring(@@VERSION, 23, 4) != '2000'
BEGIN
  select @level = [compatibility_level] from sys.databases where name = DB_name();
  SET @levelvar = @level;
  EXEC sp_dbcmptlevel ":DATABASE_NAME" @level;
END;

Yes, it the @levelvar is not necessary, yes, I could use DB_name() also in sp_dbcmptlevel, yes, I could use ALTER TABLE and yes, the IF returns wrong value in SQL 2012, but the pain is that It simply cannot be executed in SQL Studio. The error message is:

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '@level'.

How to get the local variable @level / @levelvar passed to this stored procedure?

Answer 1

There were two syntax errors in your code snippet. A missing comma in the call to sp_dbcmptlevel, and a missing END for the outer block BEGIN. This version compiles for me:

BEGIN
    DECLARE @level tinyint;
    DECLARE @levelvar tinyint;
    IF SUBSTRING(@@VERSION, 23, 4) != '2000'
    BEGIN
      SELECT @level = [compatibility_level] FROM sys.databases WHERE name = DB_NAME();
      SET @levelvar = @level;
      EXEC sp_dbcmptlevel ":DATABASE_NAME", @level;
    END;
END;