Hi Greg, Thanks for the explanation. I have used term "compile" incorrectly.
I was merely surprised that in a case where I do not have a #temp table in SELECT statement, it would *validate* all fields and would not catalogue stored procedure if it finds that tables used do not have fields referenced within the statement (not temp tables but regular tables). But, as soon as I insert a #temp table into that very same statement as one of the joined tables, after the point the #temp table is injected, no validation happens even to the other regular tables. Up to the point where #temp table is found, validation still happens. Note that tables are still validated (so cannot use dummy table names apart from #temps obviously), but fields are not. It is as if #temp table occurrence has disabled all validations that SQL server does in other cases on regular non-temp tables. If an example I have provided is tried (not on a tempdb but other database) without #temp table reference, the SP would not be catalogued/created due to errors with non-existing fields - so validation in this case catches that issue. Cheers, jano On 19 August 2012 11:42, Greg Low (GregLow.com) <[email protected]> wrote: > 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 4913fax > **** > > SQL Down Under | Web: www.sqldownunder.com**** > > ** ** > > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Jano Petras > *Sent:* Sunday, 19 August 2012 12:48 AM > *To:* [email protected] > *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**** >
