System.Data.SqlClient.SqlException: 超出了存储过程、函数、触发器或视图的最大嵌套层数(最大层数为
32
)。
首先看看嵌套的定义:
如:
嵌套1层
select * from
( select * from STUDENT ) as A
嵌套2层
select * from
( select * from
( select * from STUDENT ) as A ) as B
常犯的错误:
1 .触发器中嵌套调用
CREATE TRIGGER MoniStudentInsert
ON STUDENT
FOR INSERT , update
AS
BEGIN
update STUDENT set INSTIME = GETDATE ()
WHERE SID = ' 59E89064-8EF9-4719-8293-7F6A3F97D9AF '
END
GO
解决: 应将上面的 FOR INSERT , update 中的update去掉.
2 .定义完存储过程后未加Go结束(一般是在多个sql一次执行时出现)
如:
IF EXISTS ( SELECT * FROM dbo.sysobjects where id = OBJECT_ID (N ' [dbo].[Profu_GetStudentInfo] ' ) AND type in (N ' P ' , N ' PC ' ))
drop procedure Profu_GetStudentInfo
go
create procedure Profu_GetStudentInfo
@SID nvarchar ( 20 )
as
begin
SELECT DISTINCT SNAME,SEX
FROM STUDENT
WHERE SID = @SID
end
exec Profu_GetStudentInfo ' 10001 '
GO
解决:
将
end
exec Profu_GetStudentInfo ' 10001 '
GO
改为:
end
GO
exec Profu_GetStudentInfo ' 10001 '
也就是在定义完存储过程后加上 GO 语句.
首先看看嵌套的定义:
如:
嵌套1层
select * from
( select * from STUDENT ) as A
嵌套2层
select * from
( select * from
( select * from STUDENT ) as A ) as B
常犯的错误:
1 .触发器中嵌套调用
CREATE TRIGGER MoniStudentInsert
ON STUDENT
FOR INSERT , update
AS
BEGIN
update STUDENT set INSTIME = GETDATE ()
WHERE SID = ' 59E89064-8EF9-4719-8293-7F6A3F97D9AF '
END
GO
解决: 应将上面的 FOR INSERT , update 中的update去掉.
2 .定义完存储过程后未加Go结束(一般是在多个sql一次执行时出现)
如:
IF EXISTS ( SELECT * FROM dbo.sysobjects where id = OBJECT_ID (N ' [dbo].[Profu_GetStudentInfo] ' ) AND type in (N ' P ' , N ' PC ' ))
drop procedure Profu_GetStudentInfo
go
create procedure Profu_GetStudentInfo
@SID nvarchar ( 20 )
as
begin
SELECT DISTINCT SNAME,SEX
FROM STUDENT
WHERE SID = @SID
end
exec Profu_GetStudentInfo ' 10001 '
GO
解决:
将
end
exec Profu_GetStudentInfo ' 10001 '
GO
改为:
end
GO
exec Profu_GetStudentInfo ' 10001 '
也就是在定义完存储过程后加上 GO 语句.