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

Question

I am trying to get this snipp to work.

Targeting SQL 2005 Service Pack 2 (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, I don’t need it @levelvar, yes, I could use DB_name()it in sp_dbcmptlevel, yes, I could use it, ALTER TABLEand yes, it IFreturns the wrong value in SQL 2012, but the pain is that it simply cannot be executed in SQL Studio. Error message:

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

How to pass local variable @level/ @levelvarto this stored procedure?

Answer 1

. sp_dbcmptlevel END BEGIN. :

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;