讯息 217,级别 16,状态 1,过程 SearchClass1,第 19 行 超出了存储过程、函数、触发器或视图的最大嵌套层数(最大层数为 32)
消息 217,级别 16,状态 1,过程 SearchClass1,第 19 行 超出了存储过程、函数、触发器或视图的最大嵌套层数(最大层数为 32)。
USE [LayerManager]
GO
/****** 某班 在全年级前N名中,总分、各科 占有多少人数******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[SearchClass1]
@TableName varchar(50),
@class varchar(10),
@N varchar(100),
@Subject varchar(100)
as
declare @sql varchar(max)
declare @str varchar(10)
declare @strlength varchar(100)
set @strlength= (LEN( right(@Subject,(len(@Subject)-2)))/3)+1;
begin
set @sql='select bj='+@class+','+@subject+' from
(select top 1 bj='+@class+' from ['+@TableName+'] where Bj='+@class+')p '
declare @i varchar(10)
set @i=1
while @i<=@strlength
begin
set @str= [dbo].[Get_StrArrayStrOfIndex](@Subject,',',@i)
set @sql += 'left join
(select Bj=1,'+@str+'=COUNT(*) from (
select top '+@N+' bj,'+@str+' from ['+@TableName+'] order by '+@str+' desc) '+@str+' where '+@str+'.Bj=1 )p'+@i+'
on p.Bj=p'+@i+'.Bj '
set @i=@i+1
end
print @sql
exec(@sql)
end
exec [dbo].[SearchClass1] '2013级月考20140325成绩','1','100','zf,sx,yw'
------解决方案--------------------
看红字部分
ALTER PROCEDURE [dbo].[SearchClass1]
@TableName VARCHAR(50) ,
@class VARCHAR(10) ,
@N VARCHAR(100) ,
@Subject VARCHAR(100)
AS
DECLARE @sql VARCHAR(MAX)
DECLARE @str VARCHAR(10)
DECLARE @strlength VARCHAR(100)
SET @strlength = ( LEN(RIGHT(@Subject, ( LEN(@Subject) - 2 ))) / 3 ) + 1;
BEGIN
SET @sql = 'select bj=' + @class + ',' + @subject + ' from
(select top 1 bj=' + @class + ' from [' + @TableName + '] where Bj=' + @class
+ ')p '
DECLARE @i VARCHAR(10)
SET @i = 1
WHILE @i <= @strlength
BEGIN
SET @str = [dbo].[Get_StrArrayStrOfIndex](@Subject, ',', @i)
SET @sql += 'left join (select Bj=1,' + @str + '=COUNT(*) from (select top ' + @N + ' bj,' + @str + ' from [' + @TableName + '] order by '
+ @str + ' desc) ' + @str + ' where ' + @str + '.Bj=1 )p' + @i + 'on p.Bj=p' + @i + '.Bj '
SET @i = @i + 1
END
PRINT @sql
EXEC(@sql)
END
GO---看代码好像是因为你把exec也包在里面,导致不停嵌套,你用这句试试
EXEC [dbo].[SearchClass1] '2013级月考20140325成绩', '1', '100', 'zf,sx,yw'
USE [LayerManager]
GO
/****** 某班 在全年级前N名中,总分、各科 占有多少人数******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[SearchClass1]
@TableName varchar(50),
@class varchar(10),
@N varchar(100),
@Subject varchar(100)
as
declare @sql varchar(max)
declare @str varchar(10)
declare @strlength varchar(100)
set @strlength= (LEN( right(@Subject,(len(@Subject)-2)))/3)+1;
begin
set @sql='select bj='+@class+','+@subject+' from
(select top 1 bj='+@class+' from ['+@TableName+'] where Bj='+@class+')p '
declare @i varchar(10)
set @i=1
while @i<=@strlength
begin
set @str= [dbo].[Get_StrArrayStrOfIndex](@Subject,',',@i)
set @sql += 'left join
(select Bj=1,'+@str+'=COUNT(*) from (
select top '+@N+' bj,'+@str+' from ['+@TableName+'] order by '+@str+' desc) '+@str+' where '+@str+'.Bj=1 )p'+@i+'
on p.Bj=p'+@i+'.Bj '
set @i=@i+1
end
print @sql
exec(@sql)
end
exec [dbo].[SearchClass1] '2013级月考20140325成绩','1','100','zf,sx,yw'
------解决方案--------------------
看红字部分
ALTER PROCEDURE [dbo].[SearchClass1]
@TableName VARCHAR(50) ,
@class VARCHAR(10) ,
@N VARCHAR(100) ,
@Subject VARCHAR(100)
AS
DECLARE @sql VARCHAR(MAX)
DECLARE @str VARCHAR(10)
DECLARE @strlength VARCHAR(100)
SET @strlength = ( LEN(RIGHT(@Subject, ( LEN(@Subject) - 2 ))) / 3 ) + 1;
BEGIN
SET @sql = 'select bj=' + @class + ',' + @subject + ' from
(select top 1 bj=' + @class + ' from [' + @TableName + '] where Bj=' + @class
+ ')p '
DECLARE @i VARCHAR(10)
SET @i = 1
WHILE @i <= @strlength
BEGIN
SET @str = [dbo].[Get_StrArrayStrOfIndex](@Subject, ',', @i)
SET @sql += 'left join (select Bj=1,' + @str + '=COUNT(*) from (select top ' + @N + ' bj,' + @str + ' from [' + @TableName + '] order by '
+ @str + ' desc) ' + @str + ' where ' + @str + '.Bj=1 )p' + @i + 'on p.Bj=p' + @i + '.Bj '
SET @i = @i + 1
END
PRINT @sql
EXEC(@sql)
END
GO---看代码好像是因为你把exec也包在里面,导致不停嵌套,你用这句试试
EXEC [dbo].[SearchClass1] '2013级月考20140325成绩', '1', '100', 'zf,sx,yw'