I am trying to delete a row in parents and related child tables data.
I used below stored proc, but it's throwing error after crossing 32 nested. Please guide me, i need to delete around 10 parent rows. Thanks!
Msg 217, Level 16, State 1, Procedure spDeleteRows, Line 58
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
CREATE Procedure spDeleteRows
Recursive row delete procedure.
It deletes all rows in the table specified that conform to the criteria selected,
while also deleting any child/grandchild records and so on. This is designed to do the
same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys
table to find any child tables, then deletes the soon-to-be orphan records from them using
recursive calls to this procedure. Once all child records are gone, the rows are deleted
from the selected table. It is designed at this time to be run at the command line. It could
also be used in code, but the printed output will not be available.
@cTableName varchar(50), /* name of the table where rows are to be deleted */
@cCriteria nvarchar(1000), /* criteria used to delete the rows required
View Complete Post