CODEWITHSUNDEEP
sql-serversql-server-2017
m.edmondson
m.edmondson

Reputation: 30872

Create / Alter procedure referencing non-existing table or view

According to MSDN:

A procedure can reference tables that do not yet exist. At creation time, only syntax checking is performed. The procedure is not compiled until it is executed for the first time. Only during compilation are all objects referenced in the procedure resolved. Therefore, a syntactically correct procedure that references tables that do not exist can be created successfully; however, the procedure fails at execution time if the referenced tables do not exist.

I was always under the impression that referenced tables are checked. For example if you reference an incorrect column in an existing view it complains at compile time:

enter image description here

So what is going on here, why are columns checked within the procedure but not views / tables?

Upvotes: 1

Views: 776

Answers (1)

Thom A
Thom A

Reputation: 95574

The document is referencing that, at the time to Stored Procedure is created, it isn't validated. Take this batch:

USE Sandbox;
GO


CREATE PROC dbo.MyProc @ID int AS
BEGIN

    SELECT *
    FROM dbo.MyTable 
    WHERE ID = @ID;
END;

GO

CREATE TABLE dbo.MyTable (ID int, 
                          SomeValue varchar(20));
INSERT INTO dbo.MyTable
VALUES(1,'asdjka'),
      (2,'asdkj');
GO

EXEC dbo.MyProc 1;
GO

DROP TABLE dbo.MyTable;
DROP PROC dbo.MyProc;

Notice that I CREATE dbo.MyProc before dbo.MyTable, even though it references that object. That's because the validity of the objects in the procedure isn't checked at the point the procedure is created or altered.

A VIEW on the other hand, is checked at the time that it is created.

CREATE TABLE dbo.MyTable (ID int, 
                          SomeValue varchar(20));
INSERT INTO dbo.MyTable
VALUES(1,'asdjka'),
      (2,'asdkj');

GO
--Fails
CREATE VIEW dbo.MyView AS

    SELECT ID,
           SomeValue,
           SomeInt
    FROM dbo.MyTable;
GO
--Fails
CREATE VIEW dbo.MyOtherView AS

    SELECT *
    FROM dbo.MyOtherTable;
GO

DROP TABLE dbo.MyTable;

Depending on the object type depends on what is validated and isn't when the DDL statement is issued.

Edit: It seems that what the OP is questioning is why do that get an error if the object does exist, but they reference a column that doesn't. For exmaple take the below batch:

USE Sandbox;
GO

CREATE TABLE dbo.MyTable (ID int, 
                          SomeValue varchar(20));
INSERT INTO dbo.MyTable
VALUES(1,'asdjka'),
      (2,'asdkj');
GO

CREATE PROC dbo.MyProc @ID int AS
BEGIN

    SELECT ID,
           SomeValue,
           AnotherValue
    FROM dbo.MyTable 
    WHERE ID = @ID;
END;
GO

DROP TABLE dbo.MyTable;
GO
DROP PROC dbo.MyProc;
GO

This fails as the column AnotherValue does not exist. This is actually covered in the very documentation you quote:

A procedure can reference tables that do not yet exist. At creation time, only syntax checking is performed.

It explicitly states you can reference a table that does not exist. It makes no mention of objects that don't, or (more specifically) columns in a table/object. Referencing a table that does exist will be validated at creation time.

Upvotes: 1

Related Questions