What leads to this strange SQL behavior?

recursion, sql, sql-server, sql-server-2005, stored-procedures

Solution

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.

Problem

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?

Original source