When you create a stored procedure, SQL Server first parses the code to check for syntax errors. If the code passes the parsing stage, successfully, SQL Server attempts to resolve the names it contains. The resolution process verifies the existence of object and column names, among other things. If the referenced objects exist, the resolution process will take place fully–that is, it will also check for the existence of the referenced column names.

If an object name exists but a column within it doesn’t, the resolution process will produce an error and the stored procedure will not be created. However, if the object doesn’t exist at all, SQL Server will create the stored procedure and defer the resolution process to run time, when the stored procedure is invoked. Of course, if a referenced object or a column is still missing when you execute the stored procedure, the code will fail. This process of postponing name resolution until run time is called deferred name resolution.

I’ll demonstrate the resolution aspects I just described. First run the following code to make sure that the usp_Proc1 procedure, the usp_Proc2 procedure, and the table T1 do not exist within tempdb:

USE tempdb;
GO
IF OBJECT_ID('dbo.usp_Proc1') IS NOT NULL
  DROP PROC dbo.usp_Proc1;
GO
IF OBJECT_ID('dbo.usp_Proc2') IS NOT NULL
  DROP PROC dbo.usp_Proc2;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;

Run the following code to create the stored procedure usp_Proc1, which refers to a table named T1, which doesn’t exist:

CREATE PROC dbo.usp_Proc1
AS

SELECT col1 FROM dbo.T1;
GO

Because table T1 doesn’t exist, resolution was deferred to run time, and the stored procedure was created successfully. If T1 does not exist when you invoke the procedure, it fails at run time. Run the following code:

EXEC dbo.usp_Proc1;

You will get the following error:

Msg 208, Level 16, State 1, Procedure usp_Proc1, Line 6
Invalid object name 'dbo.T1'.

Next create table T1 with a column called col1:

CREATE TABLE dbo.T1(col1 INT);
INSERT INTO dbo.T1(col1) VALUES(1);

Invoke the stored procedure again:

EXEC dbo.usp_Proc1;

This time it will run successfully.

Next, attempt to create a stored procedure called usp_Proc2, referring to a nonexistent column (col2) in the existing T1 table:

CREATE PROC dbo.usp_Proc2
AS

SELECT col2 FROM dbo.T1;
GO

Here, the resolution process was not deferred to run time because T1 exists. The stored procedure was not created, and you got the following error:

Msg 207, Level 16, State 1, Procedure usp_Proc2, Line 4
Invalid column name 'col2'.

When you’re done, run the following code for cleanup:

USE tempdb;
GO
IF OBJECT_ID('dbo.usp_Proc1') IS NOT NULL
  DROP PROC dbo.usp_Proc1;
GO
IF OBJECT_ID('dbo.usp_Proc2') IS NOT NULL
  DROP PROC dbo.usp_Proc2;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;