Binding errors with dependent stored procedures

Question

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.

Answer 1

Thanks SQL Kiwi, you answered my question. The temp tables are all #temp tables. After reading your comments I thought why not try rename all the temp tables and make each a unique name.

I went thought each procedure and gave each #temp table a unique name per stored procedure, then I ran the command

DBCC FREEPROCCACHE;

Then then tried running my main procedure again and all seems to be working again. SQL must be getting confused with the temp table names being the same in the all the stored procedures. What I cannot explain is why from the SSRS server the report executes and from SSMS I get errors (On our Live Environment Only)

I unfortunately have taken this system over from an outsourced development house and I would not have done things they way they did it. I have been slowly replacing some of the temp tables with CTE's & re factoring out some of the un necessary joins, in most cases this gives us better performance.