By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,842 Members | 2,440 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,842 IT Pros & Developers. It's quick & easy.

Stored Procedure Error

P: 8
Hi,

I have written a Stored Procedure. I'm getting the error as below.

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have given the procedure below.



ALTER procedure [dbo].[aspdnsf_UpdateVariant]
as
begin

DECLARE @Price money, @SalePrice money, @Inventory int, @SKUSuffix nvarchar(50)

DECLARE @ROWCOUNT as int
set @ROWCOUNT=(select count(*) from ProductVariant as Variant
where Variant.ManufacturerPartNumber = Variant.SKUSuffix)

IF(@ROWCOUNT>0)
begin

DECLARE move_Product_Variant_Cursor CURSOR FOR

select Price, SalePrice , Inventory ,SKUSuffix from ProductVariant as Variant
where Variant.ManufacturerPartNumber = Variant.SKUSuffix

OPEN move_Product_Variant_Cursor
FETCH NEXT FROM move_Product_Variant_Cursor INTO @Price, @SalePrice , @Inventory, @SKUSuffix
--Fetch next record
WHILE @@FETCH_STATUS = 0
BEGIN
-- here do something like calling a stored procedure or whatever,
-- using the variable you just set, for example:
--exec aspdnsf_UpdateVariant


-- Update ProductVariant set Price=@Price, SalePrice=@SalePrice , Inventory=@Inventory
-- where SKUSuffix=@SKUSuffix and SKUSuffix<>ManufacturerPartNumber

Update ProductVariant set Price=@Price, SalePrice=@SalePrice , Inventory=@Inventory
where ManufacturerPartNumber=@SKUSuffix and SKUSuffix<>ManufacturerPartNumber

-- now move the cursor
FETCH NEXT FROM move_Product_Variant_Cursor INTO @Price, @SalePrice , @Inventory, @SKUSuffix
--print @@FETCH_STATUS
END

CLOSE move_Product_Variant_Cursor --Close cursor
DEALLOCATE move_Product_Variant_Cursor --Deallocate cursor

end
END


Regards,
Jegan
Apr 3 '09 #1
Share this Question
Share on Google+
2 Replies


code green
Expert 100+
P: 1,726
There is a limit to how deep or how many layers
stored procedures, functions etc can call each other.
I think it is five.
It looks like you are violating this somehow.
The error may not originate from your procedure but one your procedure is actioning.
Sorry can't be of more help
Apr 7 '09 #2

ck9663
Expert 2.5K+
P: 2,878
Based on the above code, you commented out this part

--exec aspdnsf_UpdateVariant

so I can only assume the nesting error is on your CURSOR. If you ran this stored proc and you have the abovementioned code uncommented, that would be the problem. SP can call itself up to a certain level. This is to prevent endless loop. And you end up a sql server running endlessly without even knowing it.

---- CK
Apr 7 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.