Hi folks,
I have encountered a weird behaviour of SQL Server 2008 R2 yesterday, so
wanted to share this experience with the list.
If a #temp table is used in a SQL select statement within a stored
procedure, SQL server does not validate the fields at all.
An example:
create procedure test_dummyfield
as
begin
select
non_existing_field1
from
FX_ORDER ord
inner join #temp t
on t.non_existing_field2 = ord.non_existing_field3
inner join FX_ORDER_LINE ordline
on ord.non_existing_field4 = ordline.non_existing_field5
where
ord.non_existing_field6 = 120
end
This stored procedure will compile nicely, but will obviously crash on
execution. If temp table is removed, the SP cannot be created as it raises
errors about non-existing fields.
As much as I try to think of an obvious reason (apart from bug in SQL
server), I cannot see why it would not validate other tables (I do
understand that #temp cannot be always validated).
So, just be wary that this happens. I have relied upon validation when
drop/create of a procedure happened to make sure there are no issues with
fields / tables used, and this proved to be a bit of an issue obviously.
Cheers,
jano
