Why does Microsoft SQL Server check columns but not tables in stored procs?

Microsoft SQL Server seems to check column name validity, but not table name validity when defining stored procedures. If it detects that a referenced table name exists currently, it validates the column names in a statement against the columns in that table. So, for example, this will run OK:

CREATE PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
    SELECT
        Col1, Col2, Col3
    FROM
        NonExistentTable
END
GO

... as will this:

CREATE PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
    SELECT
        ExistentCol1, ExistentCol2, ExistentCol3
    FROM
        ExistentTable
END
GO

... but this fails, with 'Invalid column name':

CREATE PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
    SELECT
        NonExistentCol1, NonExistentCol2, NonExistentCol3
    FROM
        ExistentTable
END
GO

Why does SQL Server check columns, but not tables, for existence? Surely it's inconsistent; it should do both, or neither. It's useful for us to be able to define SPs which may refer to tables AND/OR columns which don't exist in the schema yet, so is there a way to turn off SQL Server's checking of column existence in tables which currently exist?

Answers


This is called deferred name resolution.

There is no way of turning it off. You can use dynamic SQL or (a nasty hack!) add a reference to a non existent table so that compilation of that statement is deferred.

CREATE PROCEDURE [dbo].[MyProcedure]
AS
BEGIN

CREATE TABLE #Dummy (c int)

    SELECT
        NonExistantCol1, NonExistantCol2, NonExistantCol3
    FROM
        ExistantTable 
    WHERE NOT EXISTS(SELECT * FROM #Dummy)    


DROP TABLE #Dummy

END
GO

Need Your Help

Connecting to google datastore from java application

java api google-app-engine gae-datastore

I want to connect to google datastore from normal java application not GAE app, then create entity, put/get properties and build GQL query.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.