sql-server - 错误：超出最大存储过程、函数、触发器或视图嵌套级别（限制 32）

Question

我有这个 SQL Server 脚本可以神奇地从表和外键关系中级联删除行。

问题是当我有 3 个表相互引用时（如下所示），我得到一个错误

超出最大存储过程、函数、触发器或视图嵌套级别（限制 32）

如何防止光标进入循环？

此处的光标继续循环，然后显示以下消息

在 32 次迭代后它会中断

输出：https ://i.stack.imgur.com/4E2mi.png

存储过程定义和表定义都链接在下面。

IF OBJECT_ID('dbo.udfGetFullQualName') IS NOT NULL
    DROP FUNCTION dbo.udfGetFullQualName;
GO

CREATE FUNCTION dbo.udfGetFullQualName
    (@ObjectId INT)
RETURNS VARCHAR (300)
AS
BEGIN
    DECLARE @schema_id AS BIGINT;

    SELECT @schema_id = schema_id
    FROM sys.tables
    WHERE object_id = @ObjectId;

    RETURN '[' + SCHEMA_NAME(@schema_id) + '].[' + OBJECT_NAME(@ObjectId) + ']';
END
GO

--============ Supporting Function dbo.udfGetOnJoinClause
IF OBJECT_ID('dbo.udfGetOnJoinClause') IS NOT NULL
    DROP FUNCTION dbo.udfGetOnJoinClause;
GO

CREATE FUNCTION dbo.udfGetOnJoinClause
    (@fkNameId INT)
RETURNS VARCHAR (1000)
AS
BEGIN
    DECLARE @OnClauseTemplate AS VARCHAR (1000);

    SET @OnClauseTemplate = '[<@pTable>].[<@pCol>] = [<@cTable>].[<@cCol>] AND ';
    DECLARE @str AS VARCHAR (1000);
    SET @str = '';

    SELECT @str = @str + REPLACE(REPLACE(REPLACE(REPLACE(@OnClauseTemplate, '<@pTable>', OBJECT_NAME(rkeyid)), '<@pCol>', COL_NAME(rkeyid, rkey)), '<@cTable>', OBJECT_NAME(fkeyid)), '<@cCol>', COL_NAME(fkeyid, fkey))
    FROM dbo.sysforeignkeys AS fk
    WHERE fk.constid = @fkNameId; 

--OBJECT_ID('FK_ProductArrearsMe_ProductArrears')
    RETURN LEFT(@str, LEN(@str) - LEN(' AND '));
END
GO

--=========== CASCADE DELETE STORED PROCEDURE dbo.uspCascadeDelete
IF OBJECT_ID('dbo.uspCascadeDelete') IS NOT NULL
    DROP PROCEDURE dbo.uspCascadeDelete;
GO

CREATE PROCEDURE dbo.uspCascadeDelete
    @ParentTableId VARCHAR(300), 
    @WhereClause VARCHAR(2000), 
    @ExecuteDelete CHAR(1) = 'N', --'N' IF YOU NEED DELETE SCRIPT
    @FromClause VARCHAR(8000)='',
    @Level INT = 0 -- TABLE NAME OR OBJECT (TABLE) ID (Production.Location) WHERE CLAUSE (Location.LocationID = 7) 'Y' IF WANT TO DELETE DIRECTLY FROM SP,  IF LEVEL 0, THEN KEEP DEFAULT
AS -- written by Daniel Crowther 16 Dec 2004 - handles composite primary keys

    SET NOCOUNT ON;
    /* Set up debug */
    DECLARE @DebugMsg AS VARCHAR (4000), 
            @DebugIndent AS VARCHAR (50);

    SET @DebugIndent = REPLICATE('---', @@NESTLEVEL) + '> ';

    IF ISNUMERIC(@ParentTableId) = 0
    BEGIN -- assume owner is dbo and calculate id
        IF CHARINDEX('.', @ParentTableId) = 0
            SET @ParentTableId = OBJECT_ID('[dbo].[' + @ParentTableId + ']');
        ELSE
            SET @ParentTableId = OBJECT_ID(@ParentTableId);
    END

    IF @Level = 0
    BEGIN
        PRINT @DebugIndent + ' **************************************************************************';
        PRINT @DebugIndent + ' *** Cascade delete ALL data from ' + dbo.udfGetFullQualName(@ParentTableId);
        IF @ExecuteDelete = 'Y'
            PRINT @DebugIndent + ' *** @ExecuteDelete = Y *** deleting data...';
        ELSE
            PRINT @DebugIndent + ' *** Cut and paste output into another window and execute ***';
    END
 
    DECLARE @CRLF AS CHAR (2);
    SET @CRLF = CHAR(13) + CHAR(10);

    DECLARE @strSQL AS VARCHAR (4000);

    IF @Level = 0
        SET @strSQL = 'SET NOCOUNT ON' + @CRLF;
    ELSE
        SET @strSQL = '';

    SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + '''';

    IF @ExecuteDelete = 'Y'
        EXECUTE (@strSQL);
    ELSE
        PRINT @strSQL;

    DECLARE curs_children CURSOR LOCAL FORWARD_ONLY FOR 
         SELECT DISTINCT 
             constid AS fkNameId, -- constraint name
             fkeyid AS cTableId
         FROM   
             dbo.sysforeignkeys AS fk
         WHERE  
             fk.rkeyid <> fk.fkeyid -- WE DO NOT HANDLE self referencing tables!!!
             AND fk.rkeyid = @ParentTableId;

    OPEN curs_children;

    DECLARE @fkNameId AS INT, 
            @cTableId AS INT, 
            @cColId AS INT, 
            @pTableId AS INT, 
            @pColId AS INT;

    FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId; --, @cColId, @pTableId, @pColId

    DECLARE @strFromClause AS VARCHAR (1000);
    DECLARE @nLevel AS INT;

    IF @Level = 0
    BEGIN
        SET @FromClause = 'FROM ' + dbo.udfGetFullQualName(@ParentTableId);
    END
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @strFromClause = @FromClause + @CRLF + '      INNER JOIN ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + '       ON ' + dbo.udfGetOnJoinClause(@fkNameId);
        SET @nLevel = @Level + 1;
        EXECUTE dbo.uspCascadeDelete @ParentTableId = @cTableId, @WhereClause = @WhereClause, @ExecuteDelete = @ExecuteDelete, @FromClause = @strFromClause, @Level = @nLevel;
        SET @strSQL = 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + @strFromClause + @CRLF + 'WHERE   ' + @WhereClause + @CRLF;
        SET @strSQL = @strSQL + 'PRINT ''---' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + '     Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF + @CRLF;
        IF @ExecuteDelete = 'Y'
            EXECUTE (@strSQL);
        ELSE
            PRINT @strSQL;
        FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId;
    --, @cColId, @pTableId, @pColId
    END
IF @Level = 0
    BEGIN
        SET @strSQL = @CRLF + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + ' TOP LEVEL PARENT TABLE''' + @CRLF;
        SET @strSQL = @strSQL + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' WHERE ' + @WhereClause + @CRLF;
        SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF;
        IF @ExecuteDelete = 'Y'
            EXECUTE (@strSQL);
        ELSE
            PRINT @strSQL;
    END
CLOSE curs_children;
DEALLOCATE curs_children;







-----------------Tables---------------------------------------
USE [test]
GO

/****** Object:  Table [dbo].[shipments]    Script Date: 20/09/2021 5:00:23 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[shipments](
    [shipId] [int] NOT NULL,
    [OrderID] [int] NOT NULL,
    [OrderNumber] [int] NOT NULL,
    [PersonID] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [shipId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[shipments]  WITH CHECK ADD FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
GO

ALTER TABLE [dbo].[shipments]  WITH CHECK ADD FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
GO

ALTER TABLE [dbo].[shipments]  WITH CHECK ADD FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([ID])
GO

ALTER TABLE [dbo].[shipments]  WITH CHECK ADD FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([ID])
GO



USE [test]
GO

/****** Object:  Table [dbo].[Persons]    Script Date: 20/09/2021 5:01:20 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Persons](
    [ID] [int] NOT NULL,
    [LastName] [varchar](255) NOT NULL,
    [FirstName] [varchar](255) NULL,
    [Age] [int] NULL,
    [eid] [varchar](100) NULL,
    [shipid] [int] NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Persons]  WITH CHECK ADD FOREIGN KEY([shipid])
REFERENCES [dbo].[shipments] ([shipId])
GO

USE [test]
GO

/****** Object:  Table [dbo].[Orders]    Script Date: 20/09/2021 5:01:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Orders](
    [OrderID] [int] NOT NULL,
    [OrderNumber] [int] NOT NULL,
    [PersonID] [int] NULL,
PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD FOREIGN KEY([PersonID])
REFERENCES [dbo].[Persons] ([ID])
GO


  [1]: https://i.stack.imgur.com/4E2mi.png

标签： sql-serverdatabasestored-proceduresforeign-keysrecursive-query