troubleshooting Question

'Nested too deeply' - sp syntax error

Avatar of naqayya
naqayya asked on
Microsoft SQL Server
6 Comments1 Solution1105 ViewsLast Modified:
I get the following error when I check the syntax of my stored procedure:

Error 170: Line 616: Incorrect syntax near ','.
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

This is my code:
----------------------------------------------------------
CREATE PROCEDURE sp_MYSP AS

declare rs cursor
read_only
for select RcvdDate, Type, Narrative, Amount from tblTemp

declare @RcvdDate datetime, @Type varchar(20), @Narrative varchar(200), @Amount money
declare @myID varchar(5), @myNarrative varchar(200)

open rs

fetch next from rs into @RcvdDate, @Type, @Narrative, @Amount

while (@@fetch_status <> -1)
begin
     if (@@fetch_status <> -2)
     begin
          set @myID = right(@Narrative,5)

          if @Type <> 'DR' and isnumeric(@myID) = 1 and @myID > 10000 and @myID < 16000
          begin
               if @myID = 13999
               begin
                              insert into tblAuto (ID, RcvdDate, Amount) values(14000, @RcvdDate, @Amount)
                          end

               else if @myID = 12081 and @Amount = 60
               begin
                    insert into tblAuto (ID, RcvdDate, Amount) values(14493, @RcvdDate, @Amount)
               end

               else
               begin
                    insert into tblAuto (ID, RcvdDate, Amount) values(@myID, @RcvdDate, @Amount)
               end
         
          end

          else if @Type <> 'DR'
          begin
                          set @myNarrative = replace(@Narrative, char(10), '')
               
               if @myNarrative = '415038/INUBIRMINGHAM UNIV'
               begin                    
                    insert into tblAuto (ID, RcvdDate, Amount) values(12100, @RcvdDate, @Amount)
               end

               else if @myNarrative = 'A   M S & D  T T/A BGC235894/INU/521'
               begin                    
                    insert into tblAuto (ID, RcvdDate, Amount) values(11662, @RcvdDate, @Amount)
               end
                        .
                        .
                        .
                        else
               begin
                    insert into tblManual (RcvdDate, Type, Narrative, Amount) values (@RcvdDate, @Type, @Narrative, @Amount)
               end

          end

          else
          begin
               insert into tblManual (RcvdDate, Type, Narrative, Amount) values (@RcvdDate, @Type, @Narrative, @Amount)
          end

     end
     
     fetch next from rs into @RcvdDate, @Type, @Narrative, @Amount
end

close rs
deallocate rs
----------------------------------------------------------

The error occurs on one of the lines that read:

insert into tblAuto (ID, RcvdDate, Amount) values(11492, @RcvdDate, @Amount)

There are about 200 else if...insert clauses in the code (where I have put the 3 vertical dots) and the error occurs on the 113th insert.

Why does this happen? The SQL books online says that you can have unlimited nests. What can I do about it?

Thanks.
ASKER CERTIFIED SOLUTION
miron

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros