Is it possible to use a Stored Procedure as a subquery in SQL Server 2008?

Question

I have two stored procedures, one of which returns a list of payments, while the other returns a summary of those payments, grouped by currency. Right now, I have a duplicated query: the main query of the stored procedure that returns the list of payments is a subquery of the stored procedure that returns the summary of payments by currency. I would like to eliminate this duplicity by making the stored procedure that returns the list of payments a subquery of the stored procedure that returns the summary of payments by currency. Is that possible in SQL Server 2008?

Answer 1

You are better off converting the first proc into a TABLE-VALUED function. If it involves multiple statements, you need to first define the return table structure and populate it.

Sample:

CREATE proc getRecords @t char(1)
as
set nocouut on;
-- other statements --
-- final select
select * from master..spt_values where type = @t
GO

-- becomes --

CREATE FUNCTION fn_getRecords(@t char(1))
returns @output table(
    name sysname,
    number int,
    type char(1),
    low int,
    high int,
    status int) as
begin
-- other statements --
-- final select
insert @output
select * from master..spt_values where type = @t
return
end;

However, if it is a straight select (or can be written as a single statement), then you can use the INLINE tvf form, which is highly optimized

CREATE FUNCTION fn2_getRecords(@t char(1))
returns table as return
-- **NO** other statements; single statement table --
select * from master..spt_values where type = @t

The second proc simply selects from the first proc

create proc getRecordsByStatus @t char(1)
as
select status, COUNT(*) CountRows from dbo.fn2_getRecords(@t)
group by status

And where you used to call

EXEC firstProc @param

to get a result, you now select from it

SELECT * FROM firstProc(@param)