Hi Jano,
That's just not how it works. SQL Server has deferred name resolution. For tables that exist, it tries to check for cannot check those that it doesn't know about. For example, try the following: USE tempdb; GO CREATE PROC dbo.AccessNoSuchTable AS SELECT NoSuchColumn FROM NoSuchTable; GO EXEC dbo.AccessNoSuchTable; Another concept is that creating (or cataloguing) a procedure doesn't compile it. That happens at execution time. Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: <http://www.sqldownunder.com/> www.sqldownunder.com From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of Jano Petras Sent: Sunday, 19 August 2012 12:48 AM To: ozdotnet@ozdotnet.com Subject: SQL Server feature or a bug? 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
