Strange bug in SQL Server 2005
Take a look to the following stored procedure (which is meant to be run against the AdventureWorks database):
CREATE PROCEDURE [dbo].MyProcedure
AS
CREATE TABLE #temp (id uniqueidentifier)
SELECT Customer.CustomerType
FROM Sales.Customer
WHERE EXISTS (SELECT * FROM #temp)
GO
Although it doesn’t make too much sense, it looks quite simple and straightforward, isn’t it? But try changing “Customer.CustomerType” in the SELECT clause to “CustomerABCDE.CustomerType” and run it (dropping it first of course). SQL Server will happily create the stored procedure without ever complaining that the identifier doesn’t exist. You can also try changing “Sales.Customer” to “Sales.CustomerWhatever” and SQL Server will not complain either.
This is something that showed up at work, in a much bigger and complex query. By removing lots of code I nailed the problem down to what you see above: using nested queries in the WHERE clause involving temporary tables seems to confuse SQL Server. I don’t see the reason of why this should happen, since the outer query has nothing to do with the inner query.
A friend suggested it could be due to the use of the “EXISTS” keyword, but it’s not (and it shouldn’t). If you change the WHERE clause to the following the stored procedure it’s still created:
CREATE PROCEDURE [dbo].MyProcedure
AS
CREATE TABLE #temp (id uniqueidentifier)
SELECT CustomerXXX.CustomerType
FROM Sales.Customer
WHERE Customer.CustomerType = (SELECT TOP 1 * FROM #temp)
GO
I searched to see if this was a known bug, but I didn’t find anything. The stored procedure will still fail when executed, but it’s a weird bug anyway.
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.
It wouldn’t be terribly useful if it did, anyway, as you could always drop the table later, leaving you with the same problem.
@Michael
Please read my reply here: http://techblog.julianhidalgo.com/2009/05/12/follow-up-to-strange-bug-in-sql-server-2005/