One of my Jr. Developer always wondered when she creates any Stored Procedure (SP) and if there is incorrect table name in the SP it creates the SP fine but while executing it gives run time error. However, if there is any valid table from database is referenced in SP with incorrect column name it will not let user create SP at all.
Question : How come when table name is incorrect SP can be created successfully but when incorrect column is used SP can not be created?
Answer : Deferred Name Resolution of database is the root cause for this issue.
When a SP is created it is syntactically checked, and if there is no error then text of SP is stored in sys.sql_module catalog. If there is any error SP is not created and entry in sys.sql_module is not created.
When a SP is executed first time, it checks for all the objects used in SP statement. IF there is any table which does not exist on database it will thrown a run time error. However, at the time of creation of SP misspelled column of existing table is referenced in SP it does not allow it and SP is not created.
Above scenario is known as Deferred Name Resolution.
Let us understand this with example.
In sample database AdventureWorks, table Projects does not exist. Running following sql SP will created without any error.
USE AdventureWorks
GO
CREATE PROCEDURE usp_TestDeferred
AS
SELECT FirstName FROM Projects
GO
Result:
——
Command(s) completed successfully.
In samples database AdventureWorks, table Employees exist but there is no column named UserName in it. Running following sql SP will not be created and it will thrown an error.
USE AdventureWorks
GO
CREATE PROCEDURE sp_Employee
AS
SELECT UserName FROM Employees
GO
Result:
——-
Msg 207, Level 16, State 1, Procedure sp_Employee, Line 3
Invalid column name ‘UserName’.
If SP is executed it will come across any object which is missing then stored procedure stops execution at that point and throws an error.
Let me know what you think about this topic and its explanation. I encourage my readers to come up with interesting questions like this.
Reference : Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
I am sorry i did not got it completely.
If the SQL does Deferred Name Resolution, then it should do for both table name and for columns name, why is it bias with the Column Name.
Great post Pinal.
Erland, SQL Server MVP has prepared a detailed paper which discusses the requirement for an option to strictly validate the TSQL code at compile time. He suggested SET STRICT_CHECK ON/OFF
He suggested an option like the “OPTION EXPLICIT” in visual basic. When this is turned on, SQL Server should perform a strict validation.
regards
Jacob
I couldn’t get it either.Can you please explain it a bit more as if it is due to SQL Deferred Name Resolution it should behave same for both table names and Column names…Appreciate if you could provide some more explanation.
Regards
Tejinder
Hi Pinal,
I appreciate the way you have brought up this issue. But my curiosity still remains about why that happens. The issue is explained well in here but I think somehow the proper reason for this issue is missing.
I look forward to hear more on this from you.
Great Job Buddy!! Keep it up!!
Hardeep Singh.
I want to commit suicide. I have a task to change the schema name of a db with over one thousand SPs. This ‘feature’ is killing me. I cannot be confident that my work is actually working. I wish there is a switch to prevent this.
Very confusing explanation by Pinal Dave. Kindly explain clearly. You said ” IF there is any table which does not exist on database it will thrown a run time error.” which contradicts the question itself, which says that a SP can be created even if the table name is incorrect