exceeding max nesting level of 32

Is there a way to reconfigure the max nesting level of 32 to something a tad bit higher. Using a cursor and in one case when this stored procedure gets called it exceeds the max nesting level of 32. Will have to rewrite but for now was wondering if that was a setting that could be reconfigured.

THANK GOODNESS NO!

Nesting more than 32 levels signifies a very bad conceptual and/or logical design.

SQL Server is designed for SET based operations. CURSOR (row-wise) operations are rarely necessary. If you were to post your code, and an explanition of the task, we might be able to help you redesign the process so that both the nesting limit can become a 'non-issue' AND the entire process can execute orders of magnitude faster.

|||I know it is bad design. This function has been a good solution till recently, we are absorbin another system that is causing the problem,and we are under extreme deadlines. Here is the code. These tasks are a parent child relation, a task can have child tasks etc

ALTER FUNCTION [dbo].[fncTaskTree]
(
@.prmTask varchar(30),
@.prmParentTask varchar(30) = '',
@.prmProcessOrder int = 0,
@.prmDepth int = 0,
@.prmSortLevel varchar(128) = '1'
)

RETURNS @.TaskTree TABLE
(
ParentTask varchar(30),
Task varchar(30),
ProcessOrder int,
Depth int,
SortLevel varchar(128),
ProcessId uniqueidentifier
)

AS
BEGIN

/* Title: fncTaskTree
Parameters: @.prmTask - Task that was entered in the Task field parameter
@.prmParentTask - Task that was selected from the Task List parameter
@.prmProcessOrder -
@.prmDepth -
@.prmSortLevel -

Returns: @.TaskTree - Table of Task Tree
Author: Dale Thompson
Created: 08/31/2004
Purpose: Recursive function to populate TaskTree Table

Update History
--
*/

Declare @.intSortLevel int
Declare @.strSortLevel varchar(128)

SET @.prmDepth = @.prmDepth + 1
SET @.intSortLevel = 0

-- Insert the Process that the function is called with into the results table

INSERT INTO @.TaskTree
SELECT @.prmParentTask, Process, @.prmProcessOrder, @.prmDepth, @.prmSortLevel, ProcessId
FROM Processes
WHERE Process = @.prmTask

-- Create cursor and loop through the children for the passed in Task

Declare @.wsTask varchar(30)
Declare @.wsParentTask varchar(30)
Declare @.wsProcessOrder int
Declare @.wsProcessId uniqueidentifier

Declare curTasks Cursor static For
Select
ProcTasks.Process,
Task,
ProcessOrder,
ProcessId
From ProcTasks
Inner Join Processes
On ProcTasks.Process = Processes.Process
Where Task = @.prmTask
Order By
ProcessOrder,
ProcTasks.SubOrder,
ProcTasks.Process

Open curTasks
Fetch Next from curTasks into @.wsTask, @.wsParentTask, @.wsProcessOrder, @.wsProcessId
While @.@.Fetch_Status = 0
BEGIN

SET @.intSortLevel = @.intSortLevel + 1
SET @.strSortLevel = @.prmSortLevel + '-' + str(@.intSortLevel,3)
INSERT INTO @.TaskTree
SELECT * FROM fncTaskTree(@.wsTask, @.wsParentTask, @.wsProcessOrder, @.prmDepth, @.strSortLevel)

Fetch Next from curTasks into @.wsTask, @.wsParentTask, @.wsProcessOrder, @.wsProcessId

END

Close curTasks
Deallocate curTasks

Return

End
|||

Katie,

I'm going to move this thread to the Transact-SQL forum where there will be more heads to work on your problem...

|||

Katie:

Are you using SQL 2000 or SQL 2005?