Stored Procedure Error

[Jeganath]

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_UpdateV ariant]
as
begin

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

DECLARE @ROWCOUNT as int
set @ROWCOUNT=(sele ct count(*) from ProductVariant as Variant
where Variant.Manufac turerPartNumber = Variant.SKUSuff ix)

IF(@ROWCOUNT>0)
begin

DECLARE move_Product_Va riant_Cursor CURSOR FOR

select Price, SalePrice , Inventory ,SKUSuffix from ProductVariant as Variant
where Variant.Manufac turerPartNumber = Variant.SKUSuff ix

OPEN move_Product_Va riant_Cursor
FETCH NEXT FROM move_Product_Va riant_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_UpdateV ariant


-- Update ProductVariant set Price=@Price, SalePrice=@Sale Price , Inventory=@Inve ntory
-- where SKUSuffix=@SKUS uffix and SKUSuffix<>Manu facturerPartNum ber

Update ProductVariant set Price=@Price, SalePrice=@Sale Price , Inventory=@Inve ntory
where ManufacturerPar tNumber=@SKUSuf fix and SKUSuffix<>Manu facturerPartNum ber

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

CLOSE move_Product_Va riant_Cursor --Close cursor
DEALLOCATE move_Product_Va riant_Cursor --Deallocate cursor

end
END


Regards,
Jegan

[code green]

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

[ck9663]

Based on the above code, you commented out this part

--exec aspdnsf_UpdateV ariant

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