MS SQL: Use a stored procedures result set in another query?

Question

I have a stored procedure I don't want to modify. It's rather large and complex, and I don't want to add any more confusion to it.

So what I would like to do is have another store procedure that calls on the big one, and uses the result set to perform further selects / joins etc.

Answer 1

You can create a user defined function which call the stored procedure you have and use it in other queries.

Answer 2

You can insert procedure's result set into table. Like this:

create procedure test
as
begin

 select 1

end

go

declare @t table
(
 id int
)

insert into @t
exec test

select * from @t -- returns one row

Answer 3

You can use a user-defined function instead:

create function table_func
()
returns table
as
    return
    (
            select top 10 * 
            from master..msreplication_options  
         )  

Then, to get your result set

select * from table_func()

If you still need to call this as a stored proc in other places, create a stored proc that wraps the user-defined function:

create procedure test_proc
as
    select * from test_func();