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

发布于 2024-10-15 21:56:53 字数 165 浏览 5 评论 0原文

我有两个存储过程,其中一个返回付款列表,另一个返回这些付款的摘要(按货币分组)。现在,我有一个重复的查询:返回付款列表的存储过程的主查询是返回按货币的付款摘要的存储过程的子查询。我想通过将返回付款列表的存储过程作为返回货币付款摘要的存储过程的子查询来消除这种口是心非。这在 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?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

勿忘心安 2024-10-22 21:56:53

您最好将第一个过程转换为表值函数。如果涉及到多条语句,需要先定义返回表结构并填充。

示例:

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;

但是,如果是直接 select (或者可以写成单个语句),那么可以使用 INLINE tvf 形式,这是高度优化的

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

第二个 proc 只是从第一个 proc 中进行选择

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

And where您以前通过调用

EXEC firstProc @param

来获取结果,现在您可以从中选择

SELECT * FROM firstProc(@param)

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)
把人绕傻吧 2024-10-22 21:56:53

您可以捕获临时表中存储过程的输出,然后在主查询中使用该表。

捕获将列 ID 和名称返回到表变量的存储过程的输出。

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

insert into @T
exec StoredProcedure

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
森罗 2024-10-22 21:56:53

将存储过程的结果插入表变量或临时表即可解决问题。

如果您尝试在 SQL Server 中从一个查询到下一个查询重复使用代码,则表函数可以提供更大的灵活性。如果您不需要传递参数或使用任何类型的流控制逻辑,那么视图就可以了。这些可以像任何其他函数、过程、视图或 t-sql 语句中的表一样使用。

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.

金兰素衣 2024-10-22 21:56:53

如果您编写了将列表返回到表值函数的过程,那么我相信您可以在子查询中使用它。

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.

萌无敌 2024-10-22 21:56:53

我会使用视图,除非它需要参数化,在这种情况下,如果可能的话,我会使用内联表值函数,除非它需要是多语句操作,您仍然可以使用表值函数,但它们通常效率较低。

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文