Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

delete rows Expand / Collapse
Author
Message
Posted Saturday, May 21, 2011 1:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781
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
Post #1112922
Posted Saturday, May 21, 2011 2:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 6,826, Visits: 13,280
Why don't you use referential integrity together with cascaded delete?



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1112928
Posted Saturday, May 21, 2011 2:19 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781
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
Post #1112930
Posted Saturday, May 21, 2011 2:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 6,826, Visits: 13,280
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.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1112932
Posted Saturday, May 21, 2011 2:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 5,358, Visits: 8,917
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1112935
Posted Sunday, May 22, 2011 10:27 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781
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.
Post #1113108
Posted Monday, May 23, 2011 9:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1113419
Posted Monday, May 23, 2011 10:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 6,826, Visits: 13,280
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.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1113457
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse