ALTER DATABASE SET COMPATIBILITY_LEVEL runs irrespective of IF EXIST condition

• gokula.p.krishnan

  SSC Journeyman

  Points: 89

  More actions

  March 5, 2015 at 8:41 pm

  #316054

  Hi,

  Problem:

  I want to set compatibility_level only when it is greater than 110.

  Solution:

  Select the compatibility level and if it is greater than 110, I alter database set compatibility level=110

  ISSUE

  Irrespective of IF Exist statement the alter database statement is executed all the time.

  Here is the sql statement

  IF EXISTS (

  SELECT * FROM sys.databases where compatibility_level >110 AND name='mydatabase'

  )

  BEGIN

  ALTER DATABASE mydatabase SET COMPATIBILITY_LEVEL = 110

  END

  What is that I am missing here ??

  please support.