Error while executing stored procedure

Posted by Gayathri under Sql Server on 8/7/2012 | Points: 10 | Views : 2713 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

This is the stored procedure i wrote.

ALTER procedure sp_Insertemployee
(@name varchar(15),@deptid varchar(15),@dname varchar(15),
@location varchar(15))
AS
BEGIN

Insert into employee values (@name,@deptid,@dname,@location)

END

and executed like this.
EXEC SP_INSERTEMPLOYEE 'DIDYA','D9234','DOTNET','CHENNAI'

and this is the error i got.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

I see no nested procedures inside this .. but why is the error?

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Pandians on: 8/7/2012 [Member] [MVP] Silver | Points: 25

0	I think you have tried to execute the whole script (ALTER Proc... EXEC Proc..) 1. Execute ALTER Proc... script first. 2. Execute EXEC Proc... (or) Please use Batch seperator (GO) between the ALTER Proc and EXEC Proc Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Gayathri, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Kirthiga on: 8/9/2012 [Member] Starter | Points: 25

0	Hi Gayathri, There is no error in your stored procedure. While executing your stored procedure script you execute your [EXEC SP_INSERTEMPLOYEE 'DIDYA','D9234','DOTNET','CHENNAI'] statement also So in your Employee table the same data has been inserted more than 32 times. For a stored procedure nesting level is 32 times That's why you get the error Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). Gayathri, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Gayathri,

There is no error in your stored procedure.

While executing your stored procedure script you execute your
[EXEC SP_INSERTEMPLOYEE 'DIDYA','D9234','DOTNET','CHENNAI'] statement also

So in your Employee table the same data has been inserted more than 32 times.

For a stored procedure nesting level is 32 times

That's why you get the error

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

Gayathri, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gayathri on: 8/9/2012 [Member] Starter | Points: 25

0	Hi krithiga, Your explanation is very much clear from the error displayed it self. but i have not nested any storedproc inside the one which i wrote. Then how or why do i get this error still? also i tried executing the statements separately.. still no use. Please help Gayathri, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Pandians on: 8/9/2012 [Member] [MVP] Silver | Points: 25

0	Can you pls post your procedure script here "sp_Insertemployee" ? You already have posted the script. But, Kindly get the procedure script again Sp_Helptext sp_Insertemployee Go Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Gayathri, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Kirthiga on: 8/9/2012 [Member] Starter | Points: 25

0	Hi Gayathri, While executing your procedure EXEC SP_INSERTEMPLOYEE 'DIDYA','D9234','DOTNET','CHENNAI' statement included in your sp_Insertemployee SP So when you again Execute EXEC SP_INSERTEMPLOYEE 'DIDYA','D9234','DOTNET','CHENNAI' externally it call your procedure inside your procedure. Likewise nesting level occurs To avoid ERROR try like this ALTER procedure sp_Insertemployee (@name varchar(15),@deptid varchar(15),@dname varchar(15), @location varchar(15)) AS BEGIN Insert into employee values (@name,@deptid,@dname,@location) END GO EXEC SP_INSERTEMPLOYEE 'DIDYA','D9234','DOTNET','CHENNAI' GO Gayathri, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Gayathri,

While executing your procedure
EXEC SP_INSERTEMPLOYEE 'DIDYA','D9234','DOTNET','CHENNAI'
statement included in your sp_Insertemployee SP

So when you again Execute EXEC SP_INSERTEMPLOYEE 'DIDYA','D9234','DOTNET','CHENNAI'
externally it call your procedure inside your procedure.

Likewise nesting level occurs

To avoid ERROR try like this

ALTER procedure sp_Insertemployee 

(@name varchar(15),@deptid varchar(15),@dname varchar(15), 

@location varchar(15)) 

AS 

BEGIN 

Insert into employee values (@name,@deptid,@dname,@location) 

END 

GO

EXEC SP_INSERTEMPLOYEE 'DIDYA','D9234','DOTNET','CHENNAI' 

GO

Gayathri, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: San.Pblr.Gct on: 8/9/2012 [Member] Starter | Points: 25

0	I am not sure why its not working for you. I created a table create table EMployee (Name varchar(10), deptid varchar(10), Dname varchar(10), Location varchar(10)) Then i created ur stored proc. create procedure sp_Insertemployee (@name varchar(15),@deptid varchar(15),@dname varchar(15), @location varchar(15)) AS BEGIN Insert into EMployee values (@name,@deptid,@dname,@location) END then executed sp. EXEC SP_INSERTEMPLOYEE 'DIDYA','D9234','DOTNET','CHENNAI' Can you start with new query window and drop the existing storedproc and create it again Gayathri, if this helps please login to Mark As Answer. \| Alert Moderator

I am not sure why its not working for you.

I created a table

create table EMployee
(Name varchar(10),
deptid varchar(10),
Dname varchar(10),
Location varchar(10))

Then i created ur stored proc.

create procedure sp_Insertemployee
(@name varchar(15),@deptid varchar(15),@dname varchar(15),
@location varchar(15))
AS
BEGIN

Insert into EMployee values (@name,@deptid,@dname,@location)

END

then executed sp.
EXEC SP_INSERTEMPLOYEE 'DIDYA','D9234','DOTNET','CHENNAI'

Can you start with new query window and drop the existing storedproc and create it again

Gayathri, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts