May 21, 2011 at 1:09 pm
Hi,
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!
error:
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 */
@iRowsAffected int OUTPUT /* number of records affected by the delete */
)
As
set nocount on
declare @cTab varchar(255), /* name of the child table */
@cCol varchar(255), /* name of the linking field on the child table */
@cRefTab varchar(255), /* name of the parent table */
@cRefCol varchar(255), /* name of the linking field in the parent table */
@cFKName varchar(255), /* name of the foreign key */
@cSQL nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */
@cChildCriteria nvarchar(1000), /* criteria to be used to delete
records from the child table */
@iChildRows int /* number of rows deleted from the child table */
/* declare the cursor containing the foreign key constraint information */
DECLARE cFKey CURSOR LOCAL FOR
SELECT SO1.name AS Tab,
SC1.name AS Col,
SO2.name AS RefTab,
SC2.name AS RefCol,
FO.name AS FKName
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id
AND FK.fkey = SC1.colid
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id
AND FK.rkey = SC2.colid
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName
OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
WHILE @@FETCH_STATUS = 0
BEGIN
/* build the criteria to delete rows from the child table. As it uses the
criteria passed to this procedure, it gets progressively larger with
recursive calls */
SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' +
@cRefTab +'] WHERE ' + @cCriteria + ')'
print 'Deleting records from table ' + @cTab
/* call this procedure to delete the child rows */
EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
END
Close cFKey
DeAllocate cFKey
/* finally delete the rows from this table and display the rows affected */
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
print @cSQL
EXEC sp_ExecuteSQL @cSQL
print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName
--delete a row, example
exec spDeleteRows 'X', 'field1 = ''234''', 0
May 21, 2011 at 2:09 pm
Why don't you use referential integrity together with cascaded delete?
May 21, 2011 at 2:19 pm
Thank you for your reply.
i did't get you exactly
But I am trying to update the sp, updating in where clause will work it seems.
WHERE SO2.Name = @cTableName
May 21, 2011 at 2:27 pm
Based on the code in the sproc you posted:
This is designed to do the same sort of thing as Access's cascade delete function.
Why don't you add a (referential) foreign key constraints with a cascade delete instead of trying to rewrite a functionality that's already there?
See BOL, section "FOREIGN KEY Constraints" for details.
May 21, 2011 at 2:35 pm
LutzM (5/21/2011)
Based on the code in the sproc you posted:This is designed to do the same sort of thing as Access's cascade delete function.
Why don't you add a (referential) foreign key constraints with a cascade delete instead of trying to rewrite a functionality that's already there?
See BOL, section "FOREIGN KEY Constraints" for details.
(Added hyperlink to above...)
You'll have to implement this with CREATE TABLE or ALTER TABLE
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 22, 2011 at 10:27 pm
Resolved.
parent table column referring itself. that is why my sp is getting error nesting level exceeded (limit 32).
I have modified SP and it worked
WHERE SO2.Name = @cTableName and so1.name <> so2.name
Thanks Lutz.
May 23, 2011 at 9:46 am
laddu4700 (5/22/2011)
Resolved.parent table column referring itself. that is why my sp is getting error nesting level exceeded (limit 32).
I have modified SP and it worked
WHERE SO2.Name = @cTableName and so1.name <> so2.name
Thanks Lutz.
"resolved" for today but you will be back at some point asking why this process is so horribly slow. At that point we will ask you again why you didn't just use referential integrity and cascading deletes instead of recreating a feature that is already available (and MUCH faster because it doesn't use cursors). You really should try to handle this with proper architecture instead of throwing more code at the problem. And by all means don't use a cursor for deletes. This looks like a generic delete procedure that will be called every time you want to delete something. Just my 2¢.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2011 at 10:20 am
laddu4700 (5/22/2011)
Resolved.parent table column referring itself. that is why my sp is getting error nesting level exceeded (limit 32).
I have modified SP and it worked
WHERE SO2.Name = @cTableName and so1.name <> so2.name
Thanks Lutz.
Nothing to be thankful for as long as you still use the sproc you have. I second Sean comments and strongly recommend to get rid of the sproc.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply