0

Possible Duplicate:
Invalid column name on sql server update after column create

I am writing queries to extract data from tables in databases that are present on thousands of machines. The software that creates these databases has gone through plenty of changes hence the newer databases might have newer tables and/or newer columns so I was hoping to write a SQL query that would check for the existence of the column in the table and if present, execute a query that involves the column. If absent, then execute a query which leaves out that column.

Unfortunately all the posts so far I have come across that deal with missing columns are looking to add the column if it's not present. I am not trying to do that. I have read-only access to the databases and changing older databases is not a possibility.

Here's one post I tried: How to check if a column exists in SQL Server table

Consider this simple query (this is not exactly what I'm trying to do but it should make things clear):

    IF COL_LENGTH('E_BU','SCID') IS NOT NULL
      BEGIN
        SELECT BUID, SCID
        FROM E_BU
      END
    ELSE
      BEGIN
        SELECT BUID
        FROM E_BU
      END

The above works just fine if executed against a newer database/table that has the SCID column, but when executed against a database/table which doesn't have that column, gives the following error:

Msg 207, Level 16, State 1, Line 5 Invalid column name 'SCID'.

Is there anything that can be done in the query to get around such a situation or will I need to perform a check in the front end itself (maybe send an extra query to the database to determine if the column is present or absent and based on that execute the appropriate query)? A similar problem to this would be in the case of an entire table also. If the table is not present the query should not be executed at all.

Community
  • 1
  • 1
Rahul Garg
  • 278
  • 1
  • 6
  • 1
    You would need to have the statements referencing the possibly non existent column(s) in a child batch (stored procedure or SQL string) so that it is compiled separately from the overall batch. – Martin Smith Oct 24 '12 at 11:36
  • I suppose you are asking me to try something like this? EXEC(' SELECT BUID, SCID FROM E_BU ') – Rahul Garg Oct 24 '12 at 12:26
  • I'm not asking you to try anything but yes that would solve your issue. – Martin Smith Oct 24 '12 at 12:29
  • In your ELSE block, couldn't you simply 'SELECT BUID, NULL as SCID FROM E_BU', then deal with the null on the frontend (assuming this is feeding a UI)? – Darth Continent Oct 24 '12 at 13:54
  • the query is not failing because of that. It tries to look for SCID in the older database and fails due to that. I did try your suggestion and verify it still fails with the same error message as before. – Rahul Garg Oct 24 '12 at 14:14

0 Answers0