This is one of the most frequently asked SQL Server Stored Procedure Interview Question in SQL Server Interview. Let me explain deferred name resolution with an example. Consider the stored procedure shown below.

Create procedure spGetCustomers
As
Begin
Select * from Customers
End

Customers table does not exist. When we execute the above SQL code, the stored procedure spGetCustomers will be successfully created without errors. But when you try to call or execute the stored procedure using Execute spGetCustomers, we will get a runtime error stating Invalid object name ‘Customers’.

So, at the time of creating stored procedures, only the syntax of the SQL code is checked. The objects used in the stored procedure are not checked for their existence. Only when we try to run the procedure, the existence of the objects is checked. So, the process of postponing, the checking of the physical existence of the objects until runtime, is called deferred name resolution in the SQL Server.

Functions in SQL server does not support deferred name resolution. If we try to create an inline table-valued function as shown below, we get an error stating Invalid object name ‘Customers’ at the time of the creation of the function itself.

Create function fnGetCustomers()
returns table
as
return Select * from Customers

So, this proves that stored procedures support deferred name resolution, whereas function does not. In fact, this is one of the major differences between functions and stored procedures in SQL Server.