It happened dozen of times when I tried to get an Estimated Execution Plan, it return object verification error, while the same Stored Procedure was working perfectly. Then what’s wrong with Estimated Execution Plan, lets create a simple procedure.
CREATE PROCEDURE Proc_TestProcedure
IF 1 =2
SELECT * FROM NoTable — NoTable doesn’t exists
On compilation and execution, above stored procedure will not return any error, because condition is never true, so it never need to exec SELECT * FROM NoTable, and to check that table exists or not. Now just try to get execution plan of
It will return error.
Msg 208, Level 16, State 1, Procedure Proc_TestProcedure, Line 8
Invalid object name ‘NoTable’.
Because on estimation, query optimizer check each and every statement separately and once it try to estimate cost for “NoTable”, which never exists, it returns above mentioned error.