我们可以在另一个存储过程中编写子函数或过程吗 [英] Can we write a sub function or procedure inside another stored procedure

问题描述

我想检查 SQL Server(2000/05/08) 是否有能力编写嵌套存储过程，我的意思是 - 在另一个存储过程中编写子函数/过程.不呼叫另一个 SP.

I want to check if SQL Server(2000/05/08) has the ability to write a nested stored procedure, what I meant is - WRITING a Sub Function/procedure inside another stored procedure. NOT calling another SP.

为什么我在考虑它 - 我的一个 SP 有重复的代码行，并且仅特定于这个 SP.所以如果我们有这个嵌套的 SP 功能，那么我可以在我的内部声明另一个子/本地过程主 SP 并将所有重复行放在其中.我可以在我的主 SP 中调用该本地 sp.我记得 Oracle SP 中有这样的功能.

Why I was thinking about it is- One of my SP is having a repeated lines of code and that is specific to only this SP.So if we have this nested SP feature then I can declare another sub/local procedure inside my main SP and put all the repeating lines in that. and I can call that local sp in my main SP. I remember such feature is available in Oracle SPs.

如果 SQL Server 也有这个功能，有人可以解释一些关于它的更多细节或提供一个链接，我可以在其中找到文档.

If SQL server is also having this feature, can someone please explain some more details about it or provide a link where I can find documentation.

提前致谢赛

推荐答案

我不建议这样做，因为每次创建一个新的执行计划都必须计算，但是是的，它绝对可以做到(一切皆有可能，但并不总是推荐).

I don't recommend doing this as each time it is created a new execution plan must be calculated, but YES, it definitely can be done (Everything is possible, but not always recommended).

这是一个例子:

CREATE PROC [dbo].[sp_helloworld]
AS
BEGIN
    SELECT 'Hello World'
    DECLARE @sSQL VARCHAR(1000)
    SET @sSQL = 'CREATE PROC [dbo].[sp_helloworld2]
            AS
            BEGIN
                SELECT ''Hello World 2''
            END'
    EXEC (@sSQL)

    EXEC [sp_helloworld2];
    DROP PROC [sp_helloworld2];
END

你会收到警告

The module 'sp_helloworld' depends on the missing object 'sp_helloworld2'.
The module will still be created; however, it cannot run successfully until
the object exists.

您可以使用上面的 EXEC('sp_helloworld2') 绕过此警告.

You can bypass this warning by using EXEC('sp_helloworld2') above.

但是如果你调用 EXEC [sp_helloworld] 你会得到结果

But if you call EXEC [sp_helloworld] you will get the results

Hello World
Hello World 2

这篇关于我们可以在另一个存储过程中编写子函数或过程吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！