sql-server - 为什么我的SQL代码抛出错误

Question

这是一个概念:当用户从前端树形 View 中选择用户列表并单击Save按钮时，包含角色到用户的映射的表必须清除具有该角色的所有用户，并重新插入具有给定角色ID的记录和用户列表。

我正在使用以下存储过程和函数:

存储过程:

ALTER PROCEDURE [dbo].[AssignRoleToUser]
    @RoleID INT = 0,
    @UserID varchar(max) = ''
AS
BEGIN
   delete from UserRole 
   where RoleID = @RoleID 
     AND UserID IN (SELECT UserID FROM UserRole)

   INSERT INTO UserRole(UserID, RoleId) 
      SELECT id, @RoleID 
      FROM dbo.CSVToTable(@UserID)
END

功能

CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE (id int not null)
AS
BEGIN
    ;-- Ensure input ends with comma
    SET @InStr = REPLACE(@InStr + ',', ',,', ',')

    DECLARE @SP INT
    DECLARE @VALUE VARCHAR(1000)

    WHILE PATINDEX('%,%', @INSTR ) <> 0 
    BEGIN
       SELECT  @SP = PATINDEX('%,%',@INSTR)
       SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
       SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')

       INSERT INTO @TempTab(id) VALUES (@VALUE)
    END

    RETURN
END     

但是我得到这个错误

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

Answer 1

错误恰恰说明了以下错误以及行号。

Msg 217, Level 16, State 1, Procedure AssignRoleToUser, Line 7

从过程 AssignRoleToUser中取出第7行

INSERT INTO UserRole(UserID, RoleId) SELECT id, @RoleID 
FROM  dbo.CSVToTable(@UserID)

可以看出，它正在调用另一个名为 dbo.CSVToTable的函数。同样， dbo.CSVToTable可能正在调用其他过程/函数，这就是嵌套深度达到最大限制的方式。错误是

同样，在过程的第6行中，不需要 AND UserID IN (Select UserID from UserRole)。如下所示

delete from UserRole where RoleID = @RoleID  

您可以运行以下查询，该查询将列出在运行 AssignRoleToUser过程时在链中被调用的所有过程(从 Get All Nested Stored Procedures观察到的查询)

SELECT  * FROM 
(SELECT  NAME AS ProcedureName, SUBSTRING(( SELECT  ', ' + OBJDEP.NAME
FROM    sysdepends
        INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID
        INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID
WHERE obj.type = 'P'
AND Objdep.type = 'P'
AND sysdepends.id = procs.object_id
ORDER BY OBJ.name

FOR
XML PATH('')
), 2, 8000) AS NestedProcedures
FROM sys.procedures  procs )InnerTab
WHERE NestedProcedures IS NOT NULL
AND NAME = 'AssignRoleToUser'