John Morehouse takes us through a catch in creating temp tables inside nested stored procedures:
SQL Server offers a lot of flexibility when working with objects like stored procedures and temporary tables. One of the capabilities is to allow for stored procedures to call other stored procedures. This is called “nesting” one stored procedure within the context of the calling stored procedure. In addition, you can instantiate a temporary table from within the parent procedure that can be utilized within the context of the child procedure.
But wait! I’m not done yet!
You can also instantiate a temporary table with the same name as the parent temporary table within the child procedure. But there is a catch!
I’ve done the former (and more frequently, had to support when somebody else did the former), but I don’t think I’ve done the latter. Mostly because it seems like it’s an invitation for problems.