SQL Server - TSQL fails when column does not exists

Question

I wrote a T-SQL Script to update existing databases in production.

Basically, if a column exists, the script must create a new table, insert existing data into the new table and finally drop the column.

1. Check if column exists in (dbo.Patient) 1.1 Create a new table (dbo.PatientPhoto) 1.2 Migrate data from dbo.Patient.Photo into dbo.PatientPhoto 1.3 Drop columns dbo.Patient.Photo

2. If column does not exist, do nothing...

The script works fine when the columns exists but fails when the columns does not exists... It looks like it enters in the "IF" clause event if the column does not exists.

The script:

-- Vérifier si la colonne Photo exists dans la table patient
IF EXISTS(SELECT * FROM sys.columns 
            WHERE Name = N'Photo' AND Object_ID = Object_ID(N'Patient'))
BEGIN
    -- Vérifier si la table PatientPhoto existe et la créer si ce n'Est pas le cas
    IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'PatientPhoto'))
        BEGIN
            CREATE TABLE [dbo].[PatientPhoto](
                Id INT IDENTITY(1,1) NOT NULL,
                PatientId UNIQUEIDENTIFIER NOT NULL,
                [Photo] [varbinary](max) NOT NULL
            )
        END


     -- Vider la table PatientPhoto
     DELETE FROM dbo.PatientPhoto

     -- Populer la table PatientPhoto avec le contenu de la table patient
     INSERT INTO PatientPhoto(PatientId, Photo)
     SELECT Id, Photo FROM dbo.Patient
     WHERE Photo IS NOT NULL

     -- Supprimer la colonne Photo de la table patient
     ALTER TABLE dbo.Patient DROP COLUMN Photo
END

Answer 1

Correct, SQL Server must be able to validate your entire query prior to execution; even if it contains an IF statement that would prevent it from ever trying to access a column that doesn't exist. It will not let this query execute if the column does not exist, because it does not validate.

One way around this is to use dynamic sql for the part of the script that requires a column that may not exist, since SQL Server does not attempt to validate dynamic sql before it is executed.