是否可以在 SQL Server 2008 中使用存储过程作为子查询?

2022-01-23 00:00:00 subquery sql-server stored-procedures

我有两个存储过程，其中一个返回付款列表，而另一个返回这些付款的摘要，按货币分组.现在，我有一个重复的查询:返回支付列表的存储过程的主查询是返回按货币支付的摘要的存储过程的子查询.我想通过使返回付款列表的存储过程成为返回按货币支付的摘要的存储过程的子查询来消除这种重复性.这在 SQL Server 2008 中是否可行?

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?

推荐答案

最好将第一个 proc 转换为 TABLE-VALUED 函数.如果涉及多条语句，需要先定义返回表结构并填充.

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.

示例:

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

-- 变成--

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;

但是如果是直接选择(或者可以写成单条语句)，那么可以使用高度优化的INLINE tvf形式

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)

相关文章