What Leads To This Strange Sql Behavior?

July 02, 2024 Post a Comment

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, '') = '')

Solution 1:

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

If you do not want that, try this:

ALTERPROCEDURE dbo.Test 
    @Valueint=nullASBEGIN

    IF (IsNull(@Value, '') ='')
        SELECT'I am NULL!'ELSESELECT'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.

Solution 2:

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.

Solution 3:

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

Solution 4:

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.

Solution 5:

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 DROPPROCEDURE 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<@MaxActNestLevelBEGIN TRY
    EXEC dbo.Nester @NestLevelOUTEND TRY
BEGIN CATCH
    PRINT 'Catch Block'
    PRINT (ERROR_NUMBER())

    SELECT@NestLevel+=1;

    IF @@NestLevel<30--AND ERROR_NUMBER() = 217BEGINEXEC dbo.Nester @NestLevelOUTENDELSE 
        THROW

END CATCH

END GO EXEC dbo.Nester;

Lab Sql