Sebastian Meine warns us about using external temporary tables in stored procedures:
When a stored procedure is compiled that is accessing an external temp table, SQL Server has no guarantee that the next time this stored procedure is called it is called from the same connection. However, if it is called from a different connection, the accessed temp table might contain significantly more (or less) data making a different execution plan preferable.
A simple way to deal with this situation is to force a recompilation every time a procedure that works with external temporary tables is executed. SQL Server is not going that route. Instead, SQL Server caches the procedure once for each connection. That can safe a significant amount of CPU resources when the procedure in question is called within a loop.
Try to avoid using external temp tables. There are some cases in which it’s a very useful construct, but