SQL 我们可以在另一个存储过程中编写子函数或过程吗
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/495700/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can we write a sub function or procedure inside another stored procedure
提问by
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.
我想检查 SQL Server(2000/05/08) 是否有能力编写嵌套存储过程，我的意思是 - 在另一个存储过程中编写子函数/过程。不呼叫另一个 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.
为什么我在考虑它 - 我的一个 SP 有重复的代码行，并且只特定于这个 SP。所以如果我们有这个嵌套的 SP 功能，那么我可以在我的主 SP 中声明另一个子/本地过程和把所有重复的行放在里面。我可以在我的主 SP 中调用该本地 sp。我记得这样的特性在 Oracle SP 中是可用的。
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.
如果 SQL Server 也具有此功能，请有人解释有关它的更多详细信息或提供一个链接，我可以在其中找到文档。
Thanks in advance Sai
提前致谢
回答by John
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).
我不建议这样做，因为每次创建时都必须计算一个新的执行计划，但是是的，它绝对可以完成（一切皆有可能，但并不总是推荐）。
Here is an example:
下面是一个例子：
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
You will get the warning
你会收到警告
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.
You can bypass this warning by using EXEC('sp_helloworld2') above.
您可以使用上面的 EXEC('sp_helloworld2') 绕过此警告。
But if you call EXEC [sp_helloworld] you will get the results
但是如果你调用 EXEC [sp_helloworld] 你会得到结果
Hello World
Hello World 2
回答by andynormancx
It does not have that feature. It is hard to see what real benefit such a feature would provide, apart from stopping the code in the nested SPROC from being called from elsewhere.
它没有那个功能。除了阻止从其他地方调用嵌套 SPROC 中的代码之外，很难看出这样的功能会提供什么真正的好处。
回答by Mike Woodhouse
Oracle's PL/SQL is something of a special case, being a language heavily based on Ada, rather than simple DML with some procedural constructs bolted on. Whether or not you think this is a good idea probably depends on your appetite for procedural code in your DBMS and your liking for learning complex new languages.
Oracle 的 PL/SQL 是一种特殊情况，它是一种大量基于 Ada 的语言，而不是带有一些程序结构的简单 DML。您是否认为这是一个好主意可能取决于您对 DBMS 中的过程代码的兴趣以及您对学习复杂的新语言的喜好。
The idea of a subroutine, to reduce duplication or otherwise, is largely foreign to other database platforms in my experience (Oracle, MS SQL, Sybase, MySQL, SQLite in the main).
根据我的经验（主要是 Oracle、MS SQL、Sybase、MySQL、SQLite），子程序的想法，以减少重复或其他方式，在很大程度上与其他数据库平台无关。
While the SQL-building proc would work, I think John's right in suggesting that you don't use his otherwise-correct answer!
虽然 SQL 构建过程会起作用，但我认为 John 建议您不要使用他原本正确的答案是正确的！
You don't say what form your repeated lines take, so I'll offer three potential alternatives, starting with the simplest:
您没有说明重复的台词采用什么形式，因此我将提供三种可能的替代方案，从最简单的开始：
Do nothing. Accept that procedural SQL is a primitive language lacking so many "essential" constructs that you wouldn't use it at all if it wasn't the only option.
Move your procedural operations outside of the DBMS and execute them in code written in a more sophisticated language. Consider ways in which your architecture could be adjusted to extract business logic from your data storage platform (hey, why not redesign the whole thing!)
If the repetition is happening in DML, SELECTs in particular, consider introducing views to slim down the queries.
Write code to generate, as part of your build process, the stored procedures. That way if the repeated lines ever need to change, you can change them in one place and automatically generate the repetition.
没做什么。接受过程 SQL 是一种原始语言，缺乏如此多的“基本”构造，如果它不是唯一的选择，你根本不会使用它。
将您的过程操作移到 DBMS 之外，并在用更复杂的语言编写的代码中执行它们。考虑可以调整架构以从数据存储平台提取业务逻辑的方式（嘿，为什么不重新设计整个事情！）
如果重复发生在 DML 中，尤其是 SELECT，请考虑引入视图来精简查询。
编写代码以生成存储过程，作为构建过程的一部分。这样，如果重复的行需要更改，您可以在一个地方更改它们并自动生成重复。
That's four. I thought of another one as I was typing; consider it a bonus.
那是四个。我在打字时想到了另一个；把它当作奖金。
回答by Look
CREATE TABLE #t1 (digit INT, name NVARCHAR(10));
GO
CREATE PROCEDURE #insert_to_t1
(
@digit INT
, @name NVARCHAR(10)
)
AS
BEGIN
merge #t1 AS tgt
using (SELECT @digit, @name) AS src (digit,name)
ON (tgt.digit = src.digit)
WHEN matched THEN
UPDATE SET name = src.name
WHEN NOT matched THEN
INSERT (digit,name) VALUES (src.digit,src.name);
END;
GO
EXEC #insert_to_t1 1,'One';
EXEC #insert_to_t1 2,'Two';
EXEC #insert_to_t1 3,'Three';
EXEC #insert_to_t1 4,'Not Four';
EXEC #insert_to_t1 4,'Four'; --update previous record!
SELECT * FROM #t1;
What we're doing here is creating a procedure that lives for the life of the connection and which is then later used to insert some data into a table.
我们在这里所做的是创建一个过程，该过程在连接的生命周期内一直存在，然后用于将一些数据插入表中。
回答by Partha
I just had a similar situation in a SQL Trigger (similar to SQL procedure) where I basically had same insert statement to be executed maximum 13 times for 13 possible key values that resulted of 1 event. I used a counter, looped it 13 times using DO WHILE and used CASE for each of the key values processing, while kept a flag to figure out when I need to insert and when to skip.
我刚刚在 SQL 触发器（类似于 SQL 过程）中遇到了类似的情况，其中我基本上有相同的插入语句，对于由 1 个事件导致的 13 个可能的键值最多执行 13 次。我使用了一个计数器，使用 DO WHILE 将它循环了 13 次，并为每个键值处理使用了 CASE，同时保留了一个标志来确定何时需要插入以及何时跳过。
回答by Brent Ozar
John's sp_helloworld does work, but here's the reason why you don't see this done more often.
John 的 sp_helloworld 确实有效，但这就是为什么您不经常看到这样做的原因。
There is a very large performance impact when a stored procedure is compiled. There's a Microsoft article on troubleshooting performance problems caused by a large number of recompiles, because this really slows your system down quite a bit:
编译存储过程时会对性能产生非常大的影响。微软有一篇文章介绍了大量重新编译导致的性能问题的故障排除，因为这确实会大大降低您的系统速度：
http://support.microsoft.com/kb/243586
http://support.microsoft.com/kb/243586
Instead of creating the stored procedure, you're better off just creating a string variable with the T-SQL you want to call, and then repeatedly executing that string variable.
与其创建存储过程，不如只用要调用的 T-SQL 创建一个字符串变量，然后重复执行该字符串变量。
Don't get me wrong - that's a pretty bad performance idea too, but it's better than creating stored procedures on the fly. If you can persist this code in a permanent stored procedure or function and eliminate the recompile delays, SQL Server can build a single execution plan for the code once and then reuse that plan very quickly.
不要误会我的意思 - 这也是一个非常糟糕的性能想法，但它比动态创建存储过程要好。如果您可以将此代码保存在永久存储过程或函数中并消除重新编译延迟，则 SQL Server 可以为代码构建一个单一的执行计划，然后非常快速地重用该计划。
回答by alex martins
it would be very nice if MS develops GOSUB besides GOTO, an easy thing to do!
要是MS除了GOTO还开发GOSUB就好了，简单易行！
Creating procedures or functions for "internal routines" polute objects structure.
为“内部例程”污染对象结构创建过程或函数。
I "implement" it like this
我像这样“实现”它
BODY1:
身体 1：
goto HEADER HEADER_RET1:
转到 HEADER HEADER_RET1：
insert into body ...
goto BODY1_RET
BODY2:
身体2：
goto HEADER HEADER_RET2:
转到 HEADER HEADER_RET2：
INSERT INTO body....
goto BODY2_RET
HEADER:
标题：
insert into header
if @fork=1 goto HEADER_RET1
if @fork=2 goto HEADER_RET2
select 1/0 --flow check!
回答by ARLibertarian
I too had need of this. I had two functions that brought back case counts to a stored procedure, which was pulling a list of all users, and their case counts.
我也有这个需要。我有两个函数可以将案例计数带回存储过程，该过程正在提取所有用户及其案例计数的列表。
Along the lines of
沿着
select name, userID, fnCaseCount(userID), fnRefCount(UserID)
from table1 t1
left join table2 t2
on t1.userID = t2.UserID
For a relatively tiny set (400 users), it was calling each of the two functions one time. In total, that's 800 calls out from the stored procedure. Not pretty, but one wouldn't expect a sql server to have a problem with that few calls.
对于一个相对较小的集合（400 个用户），它一次调用这两个函数中的每一个。总共有 800 次从存储过程中调用。不漂亮，但人们不会期望 sql server 有那么几个调用的问题。
This was taking over 4 minutes to complete.
这需要 4 多分钟才能完成。
Individually, the function call was nearly instantaneous. Even 800 near instantaneous calls should be nearly instantaneous.
个别地，函数调用几乎是瞬时的。即使是 800 个近乎瞬时的呼叫也应该是近乎瞬时的。
All indexes were in place, and SSMS suggested no new indexes when the execution plan was analyzed for both the stored procedure and the functions.
所有索引都已就位，并且在分析存储过程和函数的执行计划时，SSMS 建议没有新索引。
I copied the code from the function, and put it into the SQL query in the stored procedure. But it appears the transition between sp and function is what ate up the time.
我从函数中复制了代码，并将其放入存储过程中的 SQL 查询中。但似乎 sp 和 function 之间的过渡是消耗时间的原因。
Execution time is still too high at 18 seconds, but allows the query to complete within our 30 second time out window.
18 秒的执行时间仍然太长，但允许查询在我们的 30 秒超时窗口内完成。
If I could have had a sub procedure it would have made the code prettier, but still may have added overhead.
如果我可以有一个子过程，它会使代码更漂亮，但仍然可能会增加开销。
I may next try to move the same functionality into a view I can use in a join.
接下来我可能会尝试将相同的功能移动到我可以在连接中使用的视图中。
select t1.UserID, t2.name, v1.CaseCount, v2.RefCount
from table1 t1
left join table2 t2
on t1.userID = t2.UserID
left join vwCaseCount v1
on v1.UserID = t1.UserID
left join vwRefCount v2
on v2.UserID = t1.UserID
Okay, I just created views from the functions, so my execution time went from over 4 minutes, to 18 seconds, to 8 seconds. I'll keep playing with it.
好的，我刚刚从函数创建了视图，所以我的执行时间从 4 分钟到 18 秒，再到 8 秒。我会继续玩它。
回答by LukeH
I agree with andynormancx that there doesn't seem to be much point in doing this.
我同意 andynormancx 的观点，这样做似乎没有多大意义。
If you really want the shared code to be contained inside the SP then you could probably cobble something together with GOTOor dynamic SQL, but doing it properly with a separate SP or UDF would be better in almost every way.
如果您真的希望将共享代码包含在 SP 中，那么您可能可以将某些东西与GOTO或动态 SQL拼凑在一起，但是使用单独的 SP 或 UDF 正确地完成它几乎在所有方面都会更好。
回答by LukeH
Thank you all for your replies! I'm better off then creating one more SP with the repeating code and call that, which is the best way interms of performance and look wise.
谢谢大家的回复！我最好再创建一个带有重复代码的 SP 并调用它，这是性能和外观方面的最佳方式。
