Possible to create a stored procedure which loops through another and unions?

I have a procedure in a 3rd party database called GetDocumentHistory. This procedure returns all kind of information about a document and it's history. This procedure accepts DocumentId as the parameter - and does all kinds of internal business logic. When this executes I get everything I need back.

What I need to do is pass this procedure a DocumentId, but I have many DocumentIds I need to run it for.

I am looking to write my own procedure which accepts OwnerId, which I find all the DocumentIds associated with that owner, and call the GetDocumentHistory procedure individually for each DocumentId I found. I would then like the results of all executions of the GetDocumentHistory procedure to be returned from my procedure in one big resultset.

The GetDocumentHistory procedure cannot be easily开发者_运维百科 modified to return all of these records, and it's not a simple SELECT in there but lots of loading variables and doing things with them.

Is this possible to loop and union the results?

Thank you so much for any help.

---

You could try insert ... exec. It does require you to specify exactly what the stored procedure returns. Here's an example:

if OBJECT_ID('dbo.GetDocumentHistory') is not null
    drop procedure dbo.GetDocumentHistory
go
create procedure dbo.GetDocumentHistory(
    @DocumentId int)
as
    select  'Ghost Writer' as Author
    ,       @DocumentId as Number
go
if OBJECT_ID('dbo.GetMultiDocHistory') is not null
    drop procedure dbo.GetMultiDocHistory
go
create procedure dbo.GetMultiDocHistory(
    @a int
,   @b int)
as begin
    declare @t table (name varchar(50), number int)

    declare @i int
    set @i = @a
    while @i <= @b
        begin
        insert @t exec dbo.GetDocumentHistory @i
        set @i = @i + 1
        end

    select name, number from @t
end
go
exec dbo.GetMultiDocHistory 1, 3

This prints:

name             number
Ghost Writer     1
Ghost Writer     2
Ghost Writer     3