SQL TRY-CATCH and the missing columns

Question

I have the following (SQL Server 2005)

DECLARE @L_ID_FOO_BAR INT
BEGIN TRY
 SELECT @L_ID_FOO_BAR = IDFOO 
 FROM BAR
 WHERE IDFOO = 5
END TRY
BEGIN CATCH
 SELECT @L_ID_FOO_BAR = NULL
END CATCH

in the BAR table I could have (on old databases) or not (in some more recennt databases) the IDFOO column. So, for the missing IDFOO column I'd like to leave @L_ID_FOO_BAR = NULL, in case if that column exists select the respective IDFOO.

However, when executing the script on bases without that column I obtain:

Invalid column name: 'IDFOO'

I already surrounded the script with

IF EXISTS(SELECT 1 FROM SYSCOLUMNS 
WHERE ID = OBJECT_ID('BAR') AND NAME = 'IDFOO') 

but this didn't help, it complains about the invalid column...

Questions
a) What to do to make work this script on both databases, with or without that column?
b) Why does try-catch not hide the invalid column error?

Answer 1

Question (b) is easier to answer - what you are facing there is a compile-time check, which trumps TRY-CATCH which work at run-time.

For (a)

DECLARE @L_ID_FOO_BAR INT;
DECLARE @SQL NVARCHAR(MAX) = '
 SELECT @L_ID_FOO_BAR = IDFOO 
 FROM BAR
 WHERE IDFOO = 5';
BEGIN TRY
 EXEC sp_executesql @SQL, N'@L_ID_FOO_BAR INT output', @L_ID_FOO_BAR output;
END TRY
BEGIN CATCH
 SELECT @L_ID_FOO_BAR = NULL -- redundant? it starts with NULL
END CATCH

• serhio: little fix: DECLARE @SQL NVARCHAR(MAX) *SET @SQL=...*
• RichardTheKiwi: egads.. 2005. How did I miss that :)

Answer 2

I would check syscolumns or information_schema for the existence of the column, then build a dynamic sql query that you then execute with sp_executesql with or without the missing column.

• podiluska: Create a view on the old databases that mimics the missing field then.