是什么导致了这种奇怪的 SQL 行为?

问题描述：

运行 SQL 2005 X64.

首先，在数据库上创建以下存储过程:

First, create the following stored proc on a database:

CREATE PROCEDURE dbo.Test 
    @Value int = null

AS

BEGIN

    IF (IsNull(@Value, '') = '')
        SELECT '*I am NULL!*'
    ELSE
        SELECT 'I am ' + CONVERT(varchar(20), @Value)

END

尝试按如下方式执行上述过程，您会得到以下结果:

Try executing the above proc as follows, and you get the result below:

EXEC dbo.Test

我是空的！

现在，更改 proc，使 EXEC 语句成为 sproc 本身的一部分:

Now, ALTER the proc so that the EXEC statement is part of the sproc itself:

ALTER PROCEDURE dbo.Test 
    @Value int = null

AS

BEGIN

    IF (IsNull(@Value, '') = '')
        SELECT 'I am NULL!'
    ELSE
        SELECT 'I am ' + CONVERT(varchar(20), @Value)

END

EXEC dbo.Test

如果你现在执行它，你会得到...

If you execute it now, you get...

我是空的！

...无限期，直到输出因此错误而中断:

...ad infinitum until the output breaks with this error:

消息 217，级别 16，状态 1，程序测试，第 16 行最大存储过程、函数、触发器或视图超出嵌套级别(限制为 32).

Msg 217, Level 16, State 1, Procedure Test, Line 16 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

暂时忽略这根本不是标准做法，并且很可能有人只是偶然地做这样的事情，有人可以提供一些关于 SQL 2005 在什么时候思考"的低级见解执行此过程的第二个化身?

Ignoring for the moment that this isn't at all a standard practice and that most likely someone would do something like this only by accident, could someone please provide some low-level insight on what SQL 2005 is "thinking" when the second incarnation of this proc is executed?

答

您的代码按预期运行.该过程递归地调用自己.

Your code is behaving as expected. The procedure is calling itself recursively.

如果你不想要那样，试试这个:

If you do not want that, try this:

ALTER PROCEDURE dbo.Test 
    @Value int = null

AS

BEGIN

    IF (IsNull(@Value, '') = '')
        SELECT 'I am NULL!'
    ELSE
        SELECT 'I am ' + CONVERT(varchar(20), @Value)

END

GO

EXEC dbo.Test

如果你确实想使用递归，你必须定义一个基本案例(又名退出条件")，这将使存储过程退出递归堆栈.

If you do want to use recursion, you have to define a base case (AKA "exit condition") which will make stored procedure exit the recursion stack.

是什么导致了这种奇怪的 SQL 行为?

相关推荐