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)

Answer 2

You can capture the output from a stored procedure in a temp table and then use the table in your main query.

Capture the output of a stored procedure returning columns ID and Name to a table variable.

declare @T table (ID int, Name nvarchar(50))

insert into @T
exec StoredProcedure

Answer 3

Inserting the results of your stored proc into a table variable or temp table will do the trick.

If you're trying to reuse code in SQL Server from one query to the next, you have more flexibility with Table Functions. Views are all right if you don't need to pass parameters or use any kind of flow control logic. These may be used like tables in any other function, procedure, view or t-sql statement.

Answer 4

If you made the procedure that returns the list into a table-valued function, then I believe you could use it in a sub-query.

Answer 5

I would use a view, unless it needs to be parameterized, in which case I would use an inline table-valued function if possible, unless it needs to be a multi-statement operation, where you can still use a table-valued function, but they are usually less efficient.