SQL Server Error 217

Error number

217

Severity

16 (user-correctable error)

State

1

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Database, Azure SQL Managed Instance

Category

Query Processing & Performance

Last reviewed

23 October 2026

What it means

Error 217 means a chain of procedures, functions or triggers calling each other went 32 levels deep, which is the fixed limit in SQL Server.

Every time a stored procedure calls another procedure, a function calls a function, or a trigger fires another trigger, the nesting level goes up by one; it goes down again when the inner module returns. You can read the current level with @@NESTLEVEL. When a call would take it past 32, SQL Server stops the call chain and raises 217 at severity 16. The limit is fixed: there is no setting to raise it.

The Procedure part of the header names the module that tried to make the 33rd call, which is usually the one that calls itself or sits in a loop. Real code rarely needs 32 legitimate levels, so the error nearly always points to recursion: a procedure or function that calls itself for each level of a tree, or two triggers that keep updating each other's tables.

Common causes

• A recursive stored procedure that calls itself once per level of a hierarchy or once per item, on data more than 32 levels or items deep.
• A recursive scalar function, such as a factorial or a running total written as a function calling itself.
• Two or more triggers that update each other's tables (indirect recursion), so each update fires the other trigger again. This happens while the server option nested triggers is on, which is the default.
• A trigger that updates its own table while the database option RECURSIVE_TRIGGERS is ON (direct recursion).
• A procedure or function that calls itself by mistake, for example after a copy and paste, with no stop condition at all.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

SELECT OBJECT_SCHEMA_NAME(d.referencing_id) + '.' + OBJECT_NAME(d.referencing_id) AS module_name,
       o.type_desc
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS o ON o.object_id = d.referencing_id
WHERE d.referenced_id = d.referencing_id;

SELECT OBJECT_NAME(t.parent_id) AS trigger_table,
       t.name AS trigger_name,
       t.is_disabled,
       d.referenced_entity_name AS table_it_references
FROM sys.triggers AS t
JOIN sys.sql_expression_dependencies AS d ON d.referencing_id = t.object_id
WHERE t.parent_class_desc = 'OBJECT_OR_COLUMN'
  AND d.referenced_entity_name NOT IN (N'inserted', N'deleted')
ORDER BY trigger_table, trigger_name;

SELECT name, value_in_use
FROM sys.configurations
WHERE name = N'nested triggers';

SELECT name, is_recursive_triggers_on
FROM sys.databases
WHERE name = DB_NAME();

SELECT @@NESTLEVEL AS nest_level;   -- 0 in a plain batch, 1 inside a procedure called from it

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

CREATE OR ALTER PROCEDURE dbo.GetReports @manager_id INT
AS
BEGIN
    WITH reports AS (
        SELECT id, manager_id, 1 AS lvl FROM dbo.Employees WHERE manager_id = @manager_id
        UNION ALL
        SELECT e.id, e.manager_id, r.lvl + 1
        FROM dbo.Employees AS e
        JOIN reports AS r ON e.manager_id = r.id
    )
    SELECT id, manager_id, lvl FROM reports
    OPTION (MAXRECURSION 1000);
END;

CREATE OR ALTER FUNCTION dbo.SumTo (@n INT) RETURNS INT AS
BEGIN
    RETURN CASE WHEN @n <= 0 THEN 0 ELSE @n * (@n + 1) / 2 END;
END;

CREATE OR ALTER TRIGGER trg_OrderAudit_Update ON dbo.OrderAudit AFTER UPDATE AS
BEGIN
    IF TRIGGER_NESTLEVEL() > 1 RETURN;   -- fired by another trigger: do not cascade back
    UPDATE o SET updated_at = SYSDATETIME()
    FROM dbo.Orders AS o
    JOIN inserted AS i ON i.order_id = o.id;
END;

ALTER DATABASE AppDb SET RECURSIVE_TRIGGERS OFF;

Examples

A procedure that calls itself 40 times

CREATE PROCEDURE dbo.CountDown @n INT
AS
BEGIN
    IF @n > 0
    BEGIN
        DECLARE @next INT = @n - 1;
        EXEC dbo.CountDown @next;   -- one nesting level per call
    END;
END;
GO
EXEC dbo.CountDown 40;   -- needs 41 levels; the limit is 32

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

Two triggers that keep updating each other

SET NOCOUNT ON;
CREATE TABLE dbo.Orders (id INT PRIMARY KEY, updated_at DATETIME2);
CREATE TABLE dbo.OrderAudit (order_id INT PRIMARY KEY, touched_at DATETIME2);
INSERT INTO dbo.Orders VALUES (1, SYSDATETIME());
INSERT INTO dbo.OrderAudit VALUES (1, SYSDATETIME());
GO
CREATE TRIGGER trg_Orders_Update ON dbo.Orders AFTER UPDATE AS
    UPDATE a SET touched_at = SYSDATETIME() FROM dbo.OrderAudit AS a JOIN inserted AS i ON i.id = a.order_id;
GO
CREATE TRIGGER trg_OrderAudit_Update ON dbo.OrderAudit AFTER UPDATE AS
    UPDATE o SET updated_at = SYSDATETIME() FROM dbo.Orders AS o JOIN inserted AS i ON i.order_id = o.id;
GO
UPDATE dbo.Orders SET updated_at = SYSDATETIME() WHERE id = 1;

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

A recursive scalar function

CREATE FUNCTION dbo.SumTo (@n INT) RETURNS INT AS
BEGIN
    IF @n <= 0 RETURN 0;
    RETURN @n + dbo.SumTo(@n - 1);
END;
GO
SELECT dbo.SumTo(10) AS works;   -- 11 levels: returns 55
SELECT dbo.SumTo(50) AS fails;   -- 51 levels

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

How to prevent it

Treat recursion in T-SQL modules as a warning sign: use recursive CTEs or set-based queries for hierarchies and series, and keep procedure call chains shallow. Code review can catch a module that calls itself; the self-reference query in the diagnostics finds them in an existing database.

Design triggers so they never write back to a table whose trigger writes to them, or guard them with TRIGGER_NESTLEVEL(). Keep trigger logic small and documented; for more on how triggers fire, see advanced triggers.

Common questions

Learn more on CodeWithSQL

• Stored procedures and functions
• Advanced triggers
• Advanced stored procedures

Official documentation

• Create nested triggers (Microsoft Learn)
• @@NESTLEVEL (Transact-SQL)
• CREATE TRIGGER (Transact-SQL)
• Database Engine events and errors 0 to 999

Last technically reviewed 23 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.