I had many stored procedures throughout the system using same large and complicated algorithm for collecting some data:

create procedure p1
   ...
   -- a lot of SQL code here
   ...  


create procedure pn
   ...
   -- a lot of the same SQL code here
   ...  

Obviously I wanted to refactor them by putting common code in the utility procedure:

create procedure p_common
   ...
   -- a lot of SQL code here
   ...  

create procedure p1
   ...

   exec p_common_data @id
   -- do something with the results etc.

Usually the results of stored procedure are returned as a single SELECT:

create procedure p_common
    as 
    SELECT a1, a2, from a
...

create table #results ( int a, int b )
insert into #results exec p

or are stored in the set of temporary tables:

create procedure p_common
 as 
 insert into #result_1 SELECT a1, a2, from a
 insert into #result_2 SELECT b1, b2, from b
...


create table #result_1 ( int a, int b )
create table #result_2 ( int a, int b )
exec p

Both methods call for creating the result table of proper structure in the caller procedure, so if the structure of result tables changes - all procedures p1, p2, .... need to be modified.

It would be much simplier to create result table in the utility procedure itself, but all temporary tables created in stored procedure are local to stored procedure (dropped on the exit from it). Quoting Microsoft SQL Server 200 documentation:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

As always, examining prior art and looking at the problem in broader context helps - in our case we can base the program flow (or part of it) on continuations. I strongly recommend to read  Continuations Made Simple and Illustrated and  Continuations for Curmudgeons , but I hope that that the idea would be obvious from the code below.

First, the pseudo-code implementation:

create procedure p
    as
    if ( first-time )
        call p_common, 'p'
    else
       process p_common's results
       first-time = false
 


create procedure common
    as @continuation varchar(255)
     create table #results
     fill out #results
     exec @continuation

And now implementation in T-SQL:

create procedure p
    @object_id int 
    as 
        if object_id( 'tempdb..#continuation' ) is null
             begin
             create table #continuation ( a int )
             exec common @object_id
             end
        else
             begin
             select ... from #results for xml 
             end
 
create procedure p_common
    @object_id int 
    @continuation varchar(255)
    as 
    select ... into #results ...
    exec (@continuation)

Stack view will probably be even more illustrative:

p // p processes #results
p_common // p_common creates #results and call p (the continuation)
p  // call p_common