Follow-up to “Strange bug in SQL Server 2005”
My friend Michael Dorman left the following comment on my post about a bug in SQL Server 2005:
Stored Procedure creation in T-SQL doesn’t guarantee to check to see that the dependent objects exist. That’s not a bug, it’s by design.
According to the SQL Server 2005 Books Online, Deferred Name Resolution and Compilation:
Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.
Based on this the creation of the following stored procedure should fail (since the CustomerTypeX column doesn’t exist):
CREATE PROCEDURE [dbo].MyProcedure
AS
CREATE TABLE #temp (id uniqueidentifier)
SELECT Customer.CustomerTypeX
FROM Sales.Customer
WHERE EXISTS (SELECT * FROM #temp)
GO
But SQL Server 2005 creates it without any warning or error. The reference to the temporary table on the nested query seems to make it quit any effort to resolve the identifiers (either table names or column names anywhere in the query), and I see no reason of why it should.
The behavior is also not consistent to what happens when no temporary tables are involved in the nested query. If I remove the nested query and keep everything else from the example in my previous post (I could also keep the nested query and refer to another table, say Person.Contact):
CREATE PROCEDURE [dbo].MyProcedure
AS
CREATE TABLE #temp (id uniqueidentifier)
SELECT CustomerXXX.CustomerType
FROM Sales.Customer
GO
I get the following error message upon creation:
Msg 4104, Level 16, State 1, Procedure MyProcedure, Line 4
The multi-part identifier "CustomerXXX.CustomerType" could not be bound.
Why is it different? For me this is a bug.