Error while executing stored procedure

Posted by Gayathri under Sql Server on 8/7/2012 | Points: 10 | Views : 1886 | Status : [Member] | Replies : 6
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?




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response