ALTER TABLE Executing regardless of Condition Evaluational Results

Question

Below is an excerpt of a SQL Query that I am using to update a table to the correct datatypes if needed.

If NOT Exists(Select * From Information_Schema.Columns 
    Where Table_Name = N'RXINFO'
    And Table_Schema = N'scriptassist' 
    And Column_Name = N'LastChanged'    
    And DATA_Type = N'TIMESTAMP' 
    AND IsNull(CHARACTER_MAXIMUM_LENGTH, 0) = 0)
     BEGIN
      Print 'LastChanged Field  needed type updating'
      Alter Table [scriptassist].[RXINFO] Alter Column LastChanged TIMESTAMP
     END

Currently the problem is as follows:

If I run the statement With the Alter Table present SQL Server throws this error at me.

Msg 4927, Level 16, State 1, Line 12 Cannot alter column 'LastChanged' to be data type timestamp.

The problem isn't that it can't change the Datatype the problem is that it is attempting to execute that code block regardless of the evaluation of the Condition. It should evaluate to False in this case.

If I take it out, nothing happens, the print statement doesn't even fire.

The only thing that I can think of thus far is that somehow MS SQL is evaluation the SQL beforehand and determining if all the code paths can execute, and since they can't it throws the error. However this doesn't make that much sense.

Answer 1

SQL Server parses your SQL before it executes it. The error is raised during parsing.

To delay parsing until the line is actually run, use exec:

exec ('Alter Table [scriptassist].[RXINFO] Alter Column LastChanged TIMESTAMP')

Answer 2

I believe you're getting this error because SQL cannot perform a conversion from the previous datatype of your TimeStamp column to an actual TimeStamp datatype. You'll need to drop and then add the column instead.

If NOT Exists(Select * From Information_Schema.Columns 
    Where Table_Name = N'RXINFO'
    And Table_Schema = N'scriptassist' 
    And Column_Name = N'LastChanged'    
    And DATA_Type = N'TIMESTAMP' 
    AND IsNull(CHARACTER_MAXIMUM_LENGTH, 0) = 0)
     BEGIN
      Print 'LastChanged Field  needed type updating'
      Alter Table [scriptassist].[RXINFO] Drop Column LastChanged
      Alter Table [scriptassist].[RXINFO] Add LastChanged TimeStamp
     END