What leads to this strange SQL behavior?

Running 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

I am NULL!

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...

I am NULL!

I am NULL!

I am NULL!

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

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

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.

---

The recursion is because everything is being considered part of the proc, not just the BEGIN to END block.

From my comment:

No great mystery. It's going to treat everything until the next GO or other indicator of the end of the batch as part of the proc. The outermost BEGIN and END are not required syntax as part of the procedure.

---

It's called recursion, as others have mentioned.

You can avoid it as @Adrian has shown (using 'GO' to prevent the sp from calling itself), or you can also escape it using a control structure...

Here's a sample / experiment you can study if you want to learn about recursion: http://msdn.microsoft.com/en-us/library/aa175801.aspx

---

It allows for 32 nested calls. and with every Exec call you are nesting it forever. So think recursively.

Exec proc Select Exec Select exec Infinitely.

once it reaches the 32nd nested calls it hits its maximum and says whoa i can not continue.

---

My reading of the question is not "Why is my SP exhibiting recursion?" but "Why is recursion limited to 32 and how do i get around that?"

I had completely forgotten that SQL Recursion dies on you like that.

An answer I just worked out is to make use of TRY-CATCH and @@NestLevel. The below is a small demonstrator rig. In your code it would be far better to have an independent end condition, for example running out of chunks to process.

My code has been mangled by the editor, I have no time to work round your issues.

BEGIN TRY DROP PROCEDURE dbo.Nester END TRY BEGIN CATCH END catch

GO CREATE PROCEDURE dbo.Nester @NestLevel INT = 0 OUT AS BEGIN DECLARE @MaxActNestLevel INT = 40;

SELECT @NestLevel += 1;

PRINT (CONVERT(sysname, @@NestLevel) + '    ' + CONVERT(sysname, @NestLevel))

IF @NestLevel < @MaxActNestLevel
BEGIN TRY
    EXEC dbo.Nester @NestLevel OUT
END TRY
BEGIN CATCH
    PRINT 'Catch Block'
    PRINT (ERROR_NUMBER())

    SELECT @NestLevel += 1;

    IF @@NestLevel < 30 --AND ERROR_NUMBER() = 217
    BEGIN
        EXEC dbo.Nester @NestLevel OUT
    END
    ELSE 
        THROW

END CATCH

END GO EXEC dbo.Nester;