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.
CREATE PROCEDURE usp_TestDeferred
SELECT FirstName FROM Projects
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.
CREATE PROCEDURE sp_Employee
SELECT UserName FROM Employees
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)