Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2011-05-21 : 15:09:27
|
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 58Maximum 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 */ )Asset nocount ondeclare @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 FKNameFROM 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.idWHERE SO2.Name = @cTableNameOPEN cFKeyFETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKNameWHILE @@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 ENDClose cFKeyDeAllocate cFKey/* finally delete the rows from this table and display the rows affected */SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteriaprint @cSQLEXEC sp_ExecuteSQL @cSQLprint 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName--delete a row, example exec spDeleteRows 'X', 'field1 = ''234''', 0 |
|
jfarrugia
Yak Posting Veteran
55 Posts |
Posted - 2011-05-22 : 04:28:33
|
It seems that the number of nested calls exceeds 32 which is the limit. I suggest tracking the depth and reducing the nested calls to below 32.. if the SP calls itself, it is considered a nest - so take that into account.Hope this helps.Where software development knowledge meets the reader |
 |
|
jfarrugia
Yak Posting Veteran
55 Posts |
Posted - 2011-05-22 : 04:31:21
|
Or.. if you're using foreign keys (referential integrity amongst tables).. you can apply a cascade delete option without the use of a complicated nested SP.CREATE TABLE test_child(SUB1 INT,[NO] int,FOREIGN KEY ([NO]) REFERENCES Test_parentON DELETE CASCADE)Where software development knowledge meets the reader |
 |
|
aravindt77
Posting Yak Master
120 Posts |
Posted - 2011-05-23 : 04:35:22
|
still if you have already created the table without cascading and fed data into it..no probs... by modifying the table defintion thru the management studio console ..will be able to specify the cascading if necessary and avoid fur ther nesting .. you need only to delete the parent rows.. sql server will automatically remove its rows which are of dependend on it. |
 |
|
|
|
|
|
|