CODEWITHSUNDEEP
sql-serverstored-procedures
Nick
Nick

Reputation: 41

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

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.

Upvotes: 2

Views: 5731

Answers (3)

3Dave
3Dave

Reputation: 29041

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();

Upvotes: 2

Andrew Bezzub
Andrew Bezzub

Reputation: 16032

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

Upvotes: 2

Giorgi
Giorgi

Reputation: 30873

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

Upvotes: 1

Related Questions