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?
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With