We are getting strange errors on one of our stored procedures. This stored procedure calls other stored procedures & we are getting errors relating to columns that cannot be found in the sub procedures. For example:
Exec StoredProc1, this stored proc calls StoredProc2, StoredProc3 & StoredProc4
When I execute StoredProc1 the first time I get errors relating to columns on StoredProc2. I then execute StoredProc2 directly and it works, then I call StoredProc1 again and it complains about column Errors on StoredProc3, and so on.
I am guessing this has to do with how SQL Server has cached its execution plans for StoredProc1, & executing each stored proc in turn forces the Stored Plans / Dependencies to be updated.
How can I correct this across the board?
If I execute each dependent procedure one at a time then execute the parent it works. If I then run these commands:
CHECKPOINT; DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE;
and execute the parent procedure I get the errors again.
The stored procedures all use a temporary table with the same name but if I understand it correctly because they are in different procedures they would create separate tables.