Breaking tempdb uses into multiple procedures

For example, this query causes optimization problems with #huge_result:

create proc base_proc 
as
    select * 
        into #huge_result 
        from ...
    select * 
        from tab, 
        #huge_result where ...

You can achieve better performance by using two procedures. When the base_proc procedure calls the select_proc procedure, the optimizer can determine the size of the table:

create proc select_proc 
as
    select * 
        from tab, #huge_result where ...

create proc base_proc 
as
    select * 
        into #huge_result 
        from ...
    exec select_proc

If the processing for #huge_result requires multiple accesses, joins, or other processes, such as looping with while, creating an index on #huge_result may improve performance. Create the index in base_proc so that it is available when select_proc is optimized.