Solved

Why this error comes, Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Posted on 2014-01-03
3
4,164 Views
Last Modified: 2014-01-03
I have an cursor, it always gave nested errors where as we have on 5 subjects for B.Com and semester 1

alter procedure step1
@course varchar(200),
@semester int
as
begin
declare @tsubjectcode varchar(200)
declare @tcredits varchar(200)
declare db_step1 cursor for
select distinct subjectcode,credits from allsubjects
where course=@course and semester=@semester
order by 1
open db_step1
fetch next from db_step1 into @tsubjectcode,@tcredits
while @@fetch_status=0
begin
      select @tsubjectcode,@tcredits
fetch next from db_step1 into @tsubjectcode,@tcredits
end
close db_step1
deallocate db_step1
end

exec step1 'B.Com',1
0
Comment
Question by:searchsanjaysharma
3 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 39754939
thats for nesting
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

this means that something that you are drawing information from in your stored procedure is more than 32  nested levels deep. Its obvoiusly not in the code above. You'll have to dig to find that elsewhere in one of your other functions thats your using in this SP.
0
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
ID: 39755001
The reason is that you have exec step1 'B.Com', 1 in a line that will be part of the stored procedure.

So when you execute step1 you will be executing it and it will execute itself again while in the proc.

You need to put a GO between the end and the exec

alter procedure step1
@course varchar(200),
@semester int
as
begin
declare @tsubjectcode varchar(200)
declare @tcredits varchar(200)
declare db_step1 cursor for 
select distinct subjectcode,credits from allsubjects 
where course=@course and semester=@semester
order by 1
open db_step1
fetch next from db_step1 into @tsubjectcode,@tcredits
while @@fetch_status=0
begin
      select @tsubjectcode,@tcredits
fetch next from db_step1 into @tsubjectcode,@tcredits
end
close db_step1
deallocate db_step1
end 

GO

exec step1 'B.Com',1

Open in new window

0
 

Author Closing Comment

by:searchsanjaysharma
ID: 39755217
tx
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question