Searching the existance of one column or another gives me an error

Question

IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = 'SiteInformation' 
        AND  COLUMN_NAME = 'Number_Injectors')
    BEGIN
        SELECT [Number_Injectors] as Injectors
        FROM [BLEND].[dbo].[SiteInformation]
    END

ELSE
    BEGIN
        IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_NAME = 'SiteInformation' 
                AND  COLUMN_NAME = 'Injectors')
        BEGIN
        SELECT [Injectors] as Injectors
        FROM [BLEND].[dbo].[SiteInformation]
        END
    END

The basic premise is that I have Visual Studio code that references a table called SiteInfomation from different servers to collect information concerning a certain piece of machinery. Thing is that I found out that a couple of those servers have have different column names (Injectors and Number_Injectors). The data variable Injectors from Visual Studio is looking for columns named Injectors to collect information. When it comes to a SiteInformation table that has a column named Number_Injectors instead of Injectors, the value that is returned is NULL. Number_Injectors and Injectors are both one and the same except in name.

I checked StackOverflow and found a topic on how to check if a column exists and created the code mentioned above. The if exist portion of the code works fine but I get an error if I use this query on a server that doesn't contain one of the two column names.

Example: The SiteInformation table from Server A has the column Injectors. It would give me an error because of this code:

SELECT [Number_Injectors] as Injectors
FROM [BLEND].[dbo].[SiteInformation]

Likewise the SiteInformation table from Server B has the column Number_Injectors. It would give me an error because of this code:

SELECT [Number_Injectors] as Injectors
FROM [BLEND].[dbo].[SiteInformation]

I am a bit lost on how to correct it. It seems like both Select queries are ran at the same time despite the if-exist part. Any suggestions will be helpful.

Answer 1

The SQL compiler is going to try to validate both select statements, so you'd need to "hide" them from the compiler by embedding them in an EXEC like this:

EXEC ('SELECT [Number_Injectors] as Injectors
       FROM [BLEND].[dbo].[SiteInformation]')