SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


delete rows


delete rows

Author
Message
HBK_4700
HBK_4700
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2041 Visits: 1839
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
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23571 Visits: 13559
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
HBK_4700
HBK_4700
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2041 Visits: 1839
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
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23571 Visits: 13559
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
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21530 Visits: 10652
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
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

HBK_4700
HBK_4700
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2041 Visits: 1839
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62285 Visits: 17954
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.

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)
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23571 Visits: 13559
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search