Maximum Number of Local Variables in SQL Server SET Operation

database-internalserrorssql serversql-server-2012t-sql

I have a stored procedure that contains business logic. Inside it I have around 1609 variables (don't ask me why, this is how the engine works). I try to SET a variable to the concatenated value of all other variables. As a result during creation I get the error:

Msg 8631, Level 17, State 1, Procedure XXX, Line YYY Internal error:
Server stack limit has been reached. Please look for potentially deep
nesting in your query, and try to simplify it.

I figured out that the error is due to the number of variables that I need to use in the SET operation. I can perform the assignment by splitting it in two.

My question is are there some restrictions in this area? I checked, but I did not find any.

We checked the error described in this KB, but this is not our case. We don't use any CASE expressions inside our code. We use that temporary variable to prepare a list of values that have to be replaced using a CLR function. We updated our SQL Server to SP3 CU6 (latest up to date), but we still experience the error.

Best Answer

Msg 8631, Level 17, State 1, Line xxx
Internal error: Server stack limit has been reached.
Please look for potentially deep nesting in your query, and try to simplify it.

This error occurs with long SET or SELECT variable assignment concatenation lists due to the way SQL Server parses and binds this type of statement - as a nested list of two-input concatenations.

For example, SET @V = @W + @X + @Y + @Z is bound into a tree of the form:

ScaOp_Arithmetic x_aopAdd
    ScaOp_Arithmetic x_aopAdd
        ScaOp_Arithmetic x_aopAdd
            ScaOp_Identifier @W 
            ScaOp_Identifier @X 
        ScaOp_Identifier @Y 
    ScaOp_Identifier @Z

Each concatenation element after the first two results in an extra level of nesting in this representation.

The amount of stack space available to SQL Server determines the ultimate limit to this nesting. When the limit is exceeded, an exception is raised internally, which eventually results in the error message shown above. An example process call stack when the error is thrown is shown below:

Stack trace

Repro

DECLARE @SQL varchar(max);

SET @SQL = '
    DECLARE @S integer, @A integer = 1; 
    SET @S = @A'; -- Change to SELECT if you like

SET @SQL += REPLICATE(CONVERT(varchar(max), ' + @A'), 3410) +';'; -- Change the number 3410

-- SET @S = @A + @A + @A...
EXECUTE (@SQL);

This is a fundamental limit due to the way multiple concatenations are handled internally. It affects SET and SELECT variable assignment statements equally.

The workaround is to limit the number of concatenations performed in a single statement. This will also typically be more efficient, since compiling deep query trees is resource-intensive.

Related Question