Robert,
I don't have the specific example to hand as its in an archived project library.
From what I remember I wrote code to produce a paginated list of records from a database
Basically I had a stored procedure called usp_Paginator which accepted a page number, page length and recordset identifier.
This stored procedure called another stored procedure depending on the recordset identifier which returned a recordset which I used to populate a temporary table.
I came across and instance where I wanted to say
INSERT MyTable
exec usp_Proc @arg
The problem was that usp_Proc contained code of this type itself.
If you try and do this then SQL returns an error message warning you that you cannot nest these sorts of statements.
As with all things SQL my INSERT/Exec method was a case of AN answer rather than THE answer.