0

Why can't I perform selection operations after checking for column existence in a temporary table? 

IF OBJECT_ID(''tempdb..#tempTable.Column'') IS NOT NULL
    SELECT Column from #tempTable
--Error: Invalid column name 'Column'.

Is this error thrown because of the order of operations in SQL Server? If so, is there any way to delay the evaluation of the selection until the existence criteria is satisfied?

Myles Baker
  • 3,600
  • 2
  • 19
  • 25
  • If it is a temp table why do you need to check if a column exists? Didn't your code just create the temp table? – Sean Lange Aug 06 '14 at 15:06
  • That's a good point. The temporary table is general-purpose and the process I'm trying to improve uses a generic script. Unfortunately, I cannot redesign the process at this time. – Myles Baker Aug 06 '14 at 15:15

1 Answers1

0

This is a problem that arises because the code is compiled first and then executed afterwards. The compiler checks that all columns and tables are available.

Now, the "normal" way to get around these problems is to use dynamic SQL. Something like:

IF OBJECT_ID(''tempdb..#tempTable.Column'') IS NOT NULL
    exec sp_executesql N'SELECT Column from #tempTable';

But, this won't work in your case. The problem is that the #temptable is not understood in the context of the dynamic SQL.

Alas, I don't think you can do what you want with temporary tables. You could do it with a permanent table as:

IF OBJECT_ID('_tempTable.Column') IS NOT NULL
    exec sp_executesql N'SELECT Column from _tempTable';

You can probably guess that when I have "temporary" tables in a non-temporary database, I use _ to distinguish them from other tables.

EDIT:

If you want to determine if a column is in a temporary table, then use logic such as this:

select *
from tempdb.sys.columns
where object_id = object_id('tempdb..#mytemptable');

(Courtesy of this answer -- which I note that I had already upvoted.)

Community
  • 1
  • 1
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The parser verifies column names before executing the query, that is certainly correct. What is news to me, however, is that you could use OBJECT_ID on columns. (Actually you can't.) – Andriy M Aug 07 '14 at 12:30
  • @AndriyM . . . That's funny. I didn't even think about that. Clearly you are right. I edited the answer. – Gordon Linoff Aug 07 '14 at 12:33