SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Performing a Cascade Delete in SQL Server 7
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 04/04/2002 :  08:17:06  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
This article comes to us from Tim Young. Tim writes "One of the (few) very handy things about Access is the cascade delete function. If you delete a record from a parent table, all relating records in the child tables are also deleted. I couldn’t find any way of doing this in SQL Server 7, so I wrote a stored procedure for it." Thanks for the article Tim!

Article Link.

rkc01
Starting Member

43 Posts

Posted - 04/08/2002 :  20:31:38  Show Profile  Reply with Quote
Tim,

I gave it a test run today. Nice work.

rob

Go to Top of Page

d473566
Starting Member

Canada
23 Posts

Posted - 04/09/2002 :  10:52:06  Show Profile  Reply with Quote
This is awesome- what a timesaver!!


Go to Top of Page

Brangwyn
Starting Member

New Zealand
5 Posts

Posted - 04/25/2002 :  23:16:40  Show Profile  Reply with Quote
Just wanted to say this is fantastic ! this week I had to port a database from Sql2000 back to a sql7 installation, converting all of my cascading deletes to something else would have been a nightmare without this SP, thank you very much Tim !!

Go to Top of Page

Pinchio
Starting Member

1 Posts

Posted - 05/13/2002 :  21:09:16  Show Profile  Reply with Quote
Great article, but what about SQL2000, or does that version have this "casecade update/delete" feature built in?

Also what if anything would need to change to build this into a stored procedure that is called from ASP.

Again, great article.

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 05/14/2002 :  15:57:48  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
SQL 2000 has Cascading Updates and Deletes built in. Version 7.0 did not. You can find more details in BOL.

As for calling it from ASP, any stored procedure, including the one listed in the article can be execute from ASP. Look into the ADO Command object.

Go to Top of Page

daviddeldave
Starting Member

United Kingdom
28 Posts

Posted - 09/23/2002 :  11:56:42  Show Profile  Reply with Quote
Let me add myself as another appreciative member of SQL Team and for that lovely code which has been brilliant!!!!!

David Mercer
Go to Top of Page

mustak_shaikh
Starting Member

1 Posts

Posted - 09/26/2002 :  23:55:56  Show Profile  Reply with Quote
Declare @row int
Set @row = 1
Set rowcount 1
While @row > 0
Begin
Delete t from (select a,count(a) as c from t group by a ) as E
Where E.c > 1 and t.a = E.a
Set @row = @@rowcount
End

Go to Top of Page

GreySky
Starting Member

USA
20 Posts

Posted - 04/10/2003 :  17:11:46  Show Profile  Reply with Quote
I greatly appreciated your code on the SQL cascade delete, and indeed it works as advertised. Unfortunately, I found that if the cascade tree was deep, it took a considerable amount of time to perform the cascade.

I updated the deleter to use a temporary table to hold the primary key values as it drills down the hierarchy. This avoids highly-nested Where [fld] In (...) statements.

Following is my customized version that works *much* faster for me.

Assumption:
--All tables' primary keys are single keys of type int (unless the last table, such as an associative entity, that does not then cascade further)

CREATE PROCEDURE spCascadeDeleteLong
(
-- this cascade deleter uses temporary tables to avoid deep nesting issues
-- requires that tables have one primary key, and its type is int
@cTableName varchar(75), -- name of the table where rows are to be deleted
@cCriteria varchar(255), -- criteria used to delete the rows required
@bDeleteTopHierarchy int, -- delete top hierarchy?
@iLevel int = 0 -- execution level (do not pass this argument)
)
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
@cSQL varchar(255), -- query string passed to the sp_ExecuteSQL procedure
@cChildCriteria varchar(255), -- criteria to be used to delete records from the child table
@iLevelNew int -- new level (for recursive calling)

-- prepare the temporary table holding the pk values of the called level
IF @iLevel = 0
BEGIN
-- build the temporary table
Create Table #tblCascadeDelete (CallLevel int NOT NULL, PKValue int NOT NULL)
END


-- 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
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
WHERE SO2.Name = @cTableName

OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol

-- if an initial fetch was successful, then add the appropriate PK values to the temporary table
IF @@FETCH_STATUS = 0
BEGIN
SET @cSQL = 'INSERT INTO #tblCascadeDelete ( CallLevel, PKValue ) SELECT ' + Convert(varchar(3), @iLevel) + ' As CallLevel, [' + @cRefCol + '] As PKValue FROM [' + @cTableName + '] WHERE ' + @cCriteria
EXEC (@cSQL)
END

-- only recurse if rows inserted
IF @@RowCount > 0
BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
-- build the criteria to pass on for the next table
SET @cChildCriteria = '[' + @cCol + '] IN (SELECT [PKValue] FROM #tblCascadeDelete Where [CallLevel] = ' + Convert(varchar(3), @iLevel) + ')'

-- call this procedure to delete the child rows
SET @iLevelNew = @iLevel + 1
EXEC spCascadeDeleteLong @cTab, @cChildCriteria, 1, @iLevelNew

-- return the next values
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol
END
END

CLOSE cFKey
DEALLOCATE cFKey

-- delete the rows from this table
IF @bDeleteTopHierarchy <> 0
BEGIN
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
EXEC (@cSQL)
END

-- drop the temporary table if top level, otherwise remove values from this level to allow reuse
IF @iLevel = 0
BEGIN
Drop Table #tblCascadeDelete
END
ELSE
BEGIN
SET @cSQL = 'DELETE FROM #tblCascadeDelete WHERE CallLevel = ' + Convert(varchar(3), @iLevel)
EXEC (@cSQL)
END
Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 04/10/2003 :  20:31:30  Show Profile  Reply with Quote
Even though SQL 2000 has cascade deletes, I still want to see a version of this from nr without using cursors- c'mon nigel!

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

GreySky
Starting Member

USA
20 Posts

Posted - 04/14/2003 :  13:09:57  Show Profile  Reply with Quote
Of course you could bypass the cursor by using a counter variable and a query that involves Select Top n ... ordered descending (or another variation including Fld > LastFldValue). But why? Why use complex SQL when a cursor is clearly more appropriate

Edited by - GreySky on 04/14/2003 13:21:44
Go to Top of Page

akak1701
Starting Member

USA
2 Posts

Posted - 09/11/2003 :  15:17:05  Show Profile  Visit akak1701's Homepage  Reply with Quote
This works great except that I've run into a limitation with SQL Server. The database I'm working with is large in the number of tables and in the depth of the relationships. In fact, I've reached the limit for nested calls:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Does anyone know how to change this value? If so it will make my life much easier.

I also made changes to the following vars to prevent it from erroring out.

@cCriteria nvarchar(4000)
@cSQL nvarchar(4000)
@cChildCriteria nvarchar(4000)

Thanks,

Greg
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 09/11/2003 :  15:19:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
32 is the limit for both SQL Server 7.0 and SQL Server 2000. This is not configurable.

Tara
Go to Top of Page

GreySky
Starting Member

USA
20 Posts

Posted - 09/11/2003 :  18:23:59  Show Profile  Reply with Quote
Which version are you using? Are you using the posted version that continuously builds longer nested In ( ... ) statements? You must be using that one if you had to extend the length of the criteria string to 4000. I can just imagine how slow that design would be on your database. OMG!

You can use my version to solve your problem. For example, by essentially copying the code into the location where it recurses, you can double the *effective* stack to 64 levels.

I feel for you. I'm just happy my client likes the version I created.

David Atkins, MCP

Edited by - GreySky on 09/11/2003 18:39:43
Go to Top of Page

Wael
Starting Member

1 Posts

Posted - 05/30/2004 :  04:51:06  Show Profile  Reply with Quote
Hi,

I am a .net developer with vert weak skills in SQL and hoping someone can help me in the following question.. I am using SQL Server 2000 with VS.NET 2003

I found Tim's Article on "Performing a Cascade Delete in SQL
Server 7"

So Here is the question:-

I would like a generic stored procedure that will loop through all
tables in my database and delete records from any table that has more
than say 7 or 10 records ...

so in other words I want to shrink all tables making sure they will have at least some data in them i.e. the first 7 to 10 records for each table but at the same time I would like to maintain the correct records accordingly as some tables are linked just like you explained in your article,

is this possible, can you pls explain how.

Also the reason I need this is simply because I have a big .NET project at work with huge database and I wanted to transfer some of the database (not all of it) to my computer at home so I can work from there too..

if there is a work around this I'd really appreciate it if you let me
know.

Thanks for your help in advance,

Wael.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 05/30/2004 :  05:45:55  Show Profile  Reply with Quote
quote:
Originally posted by Wael

Also the reason I need this is simply because I have a big .NET project at work with huge database and I wanted to transfer some of the database (not all of it) to my computer at home so I can work from there too..

Wouldn't it be easier, therefore, to transfer the 10 most-recent records from the Production database to your Home database? (rather than taking a DELETE type approach)

I would just make a new, temporary, database; build a SQL script to copy relevant records, BACKUP the temporary database, RESTORE it at "Home" and then drop the temporary database.

Presumably you have to worry about relationships between tables - so if you have Orders, Order Items and Products, you will transfer the TOP 10 Orders, ALL the Order Items that are associated, and then ALL the Products used on any of those order items.

I can't see a way to easily generate a script that will do this AutoMagically, but it is not hard to create such a script manually - you will need to know which tables relate to which though.

So, perhaps something like:

Use GENERATE SQL first to SCRIPT the database, run the script on the TargetDatabase (then you will have all tables, foreign keys, etc.)

Then copy the data across. Note that you will have to insert data in LOGICAL order, otherwise the FOREIGN KEY relationships will prevent it. However, I've structured the script below so it will only do each table ONCE, so if you just keep re-running the script it will fill in any missing gaps on sucessive passes until it is all there - at which point every INSERT will return zero rows, and you are done! (But getting them in the right order will mean it will run easier/faster/with few steps next time you need it)

-- Get most recent 10 orders
INSERT INTO TargetDatabase.dbo.ORDERS
SELECT TOP 10 *
FROM   SourceDatabase.dbo.ORDERS
WHERE  NOT EXISTS (SELECT * FROM TargetDatabase.dbo.ORDERS)
ORDER BY OrderDate DESC

-- Get Order Items for sample Order set
INSERT INTO TargetDatabase.dbo.ORDER_ITEMS
SELECT *
FROM   SourceDatabase.dbo.ORDER_ITEMS S
JOIN   TargetDatabase.dbo.ORDERS T
       ON S.OrderID = T.OrderID
WHERE  NOT EXISTS (SELECT * FROM TargetDatabase.dbo.ORDER_ITEMS)

-- Get Products for sample Order Item set
INSERT INTO TargetDatabase.dbo.PRODUCTS
SELECT *
FROM   SourceDatabase.dbo.PRODUCTS S
JOIN   TargetDatabase.dbo.ORDER_ITEMS T
       ON S.ProductID = T.ProductID
WHERE   NOT EXISTS (SELECT * FROM TargetDatabase.dbo.PRODUCTS)

Kristen
Go to Top of Page

sqlsavior
Starting Member

USA
1 Posts

Posted - 08/30/2004 :  14:13:55  Show Profile  Visit sqlsavior's Homepage  Reply with Quote
quote:
Originally posted by AskSQLTeam

This article comes to us from Tim Young. Tim writes "One of the (few) very handy things about Access is the cascade delete function. If you delete a record from a parent table, all relating records in the child tables are also deleted. I couldn’t find any way of doing this in SQL Server 7, so I wrote a stored procedure for it." Thanks for the article Tim!<P>Article <a href="/item.asp?ItemID=8595">Link</a>.



This is a cool procedure, unfortunately it does not handle composite foriegn keys correctly. The following test code shows this:


create table parent
(
parent_id_1 int not null,
parent_id_2 int not null
)
go

alter table parent add constraint PK_parent
primary key (parent_id_1, parent_id_2)
go

create table child
(
parent_id_1 int not null,
parent_id_2 int not null,
child_number int not null
)
go

alter table child add constraint PK_Child primary key (parent_id_1, parent_id_2, child_number)
go

alter table child add constraint FK_Child_Parent
foreign key (Parent_id_1, parent_id_2) references parent (parent_id_1, parent_id_2)
go

create table grandchild
(
grandchild_id int not null primary key IDENTITY,
parent_id_1 int not null,
parent_id_2 int not null,
child_number int not null
)
go

alter table grandchild add constraint FK_grandchild_child
foreign key (parent_id_1, parent_id_2, child_number) references child (parent_id_1, parent_id_2, child_number)
go

create table great_grandchild
(
grandchild_id int not null,
great_grandchild_number int not null
)
go

alter table great_grandchild add constraint PK_great_grandchild primary key (grandchild_id, great_grandchild_number)
go

alter table great_grandchild add constraint FK_great_grandchild_grandchild
foreign key (grandchild_id) references grandchild (grandchild_id)
go

-- delete all
delete great_grandchild
delete grandchild
delete child
delete parent

-- add test records

-- add genealogy 1
insert parent (parent_id_1, parent_id_2) values (1, -1)
insert child (parent_id_1, parent_id_2, child_number) values (1, -1, 1)
insert child (parent_id_1, parent_id_2, child_number) values (1, -1, 2)
declare @first_grandchild_id int
insert grandchild (parent_id_1, parent_id_2, child_number) values (1, -1, 1)
select @first_grandchild_id = @@identity
select @first_grandchild_id as 'gen 1: first grandchild_id'
declare @second_grandchild_id int
insert grandchild (parent_id_1, parent_id_2, child_number) values (1, -1, 2)
select @second_grandchild_id = @@identity
select @second_grandchild_id as 'gen 1: second grandchild_id'
insert great_grandchild (grandchild_id, great_grandchild_number) select @first_grandchild_id, 1
insert great_grandchild (grandchild_id, great_grandchild_number) select @second_grandchild_id, 1


-- add genealogy 2
insert parent (parent_id_1, parent_id_2) values (100, -100)
insert child (parent_id_1, parent_id_2, child_number) values (100, -100, 100)
insert child (parent_id_1, parent_id_2, child_number) values (100, -100, 200)
declare @first_grandchild_id int
insert grandchild (parent_id_1, parent_id_2, child_number) values (100, -100, 100)
select @first_grandchild_id = @@identity
select @first_grandchild_id as 'gen 1: first grandchild_id'
declare @second_grandchild_id int
insert grandchild (parent_id_1, parent_id_2, child_number) values (100, -100, 200)
select @second_grandchild_id = @@identity
select @second_grandchild_id as 'gen 1: second grandchild_id'
insert great_grandchild (grandchild_id, great_grandchild_number) select @first_grandchild_id, 100
insert great_grandchild (grandchild_id, great_grandchild_number) select @second_grandchild_id, 100

-- add genealogy 3
insert parent (parent_id_1, parent_id_2) values (1, -100)
insert child (parent_id_1, parent_id_2, child_number) values (1, -100, 1000)
insert child (parent_id_1, parent_id_2, child_number) values (1, -100, 2000)
declare @first_grandchild_id int
insert grandchild (parent_id_1, parent_id_2, child_number) values (1, -100, 1000)
select @first_grandchild_id = @@identity
select @first_grandchild_id as 'gen 1: first grandchild_id'
declare @second_grandchild_id int
insert grandchild (parent_id_1, parent_id_2, child_number) values (1, -100, 2000)
select @second_grandchild_id = @@identity
select @second_grandchild_id as 'gen 1: second grandchild_id'
insert great_grandchild (grandchild_id, great_grandchild_number) select @first_grandchild_id, 333
insert great_grandchild (grandchild_id, great_grandchild_number) select @second_grandchild_id, 444

select * from parent
select * from child
select * from grandchild
select * from great_grandchild

declare @iRowsAffected int
select @irowsAffected = -1
exec deleteRows 'parent', ' parent_id_1 = 1 and parent_id_2 = -1 ', @iRowsAffected OUTPUT
select @iRowsAffected as 'rows affected'

-- note that now *ALL* grandchild and great_grandchild entries are gone...

select * from parent
select * from child
select * from grandchild
select * from great_grandchild


Fred <dot> Williams <at> sqlsavior <dot> com

Edited by - sqlsavior on 08/30/2004 14:19:58
Go to Top of Page

daniel_crowther
Starting Member

United Kingdom
2 Posts

Posted - 12/17/2004 :  09:36:49  Show Profile  Reply with Quote
Hi Guys,

My Christmas present to you all:

"My solution to Cascade Delete problem"

Note 1: however, does not handle self referencing FKs - who d'ya think I am: superman???
Note 2: to the guy who ran out of nest levels, do you think Note 1 might have something to do with it
Note 3: if not, what are you trying to model: the WORLD???


Merry Christmas, hope it helps,


Daniel Crowther (London UK)





USE [<DBNAME>]
GO

IF OBJECT_ID('dbo.udfGetFullQualName') IS NOT NULL DROP FUNCTION dbo.udfGetFullQualName
GO
CREATE FUNCTION dbo.udfGetFullQualName
(
	@ObjectId INTEGER
)
RETURNS VARCHAR(300)
AS
BEGIN
-- writen by Daniel Crowther 17 Dec 2004 primarily to support uspCascadeDelete (makes it more readable!!!)
	RETURN '[' + USER_NAME(OBJECTPROPERTY(@ObjectId, 'OwnerId')) + '].[' + OBJECT_NAME(@ObjectId) + ']'
END
GO






IF OBJECT_ID('dbo.udfGetOnJoinClause') IS NOT NULL DROP FUNCTION dbo.udfGetOnJoinClause
GO
CREATE FUNCTION dbo.udfGetOnJoinClause
(
	@fkNameId INTEGER
)
RETURNS VARCHAR(1000)
AS
BEGIN

-- writen by Daniel Crowther 16 Dec 2004 primarily to support uspCascadeDelete (makes it more readable!!!)
	DECLARE @OnClauseTemplate VARCHAR(1000)
	SET @OnClauseTemplate = '[<@pTable>].[<@pCol>] = [<@cTable>].[<@cCol>] AND '
	
	DECLARE @str VARCHAR(1000)
	SET @str = ''
	SELECT	
	/*	fk_name = OBJECT_NAME(constid),	-- constraint name
		cTableId = OBJECT_NAME(fkeyid),	-- child table
		c_col_id = COL_NAME(fkeyid, fkey),	-- child table column
		p_table_id = OBJECT_NAME(rkeyid),	-- parent table
		p_col_id = COL_NAME(rkeyid, rkey)	-- parent table column
	,	
	*/	@str = @str +
			REPLACE(
				REPLACE(
					REPLACE(
						REPLACE(@OnClauseTemplate,
							'<@pTable>', 
							OBJECT_NAME(rkeyid)
						),
						'<@pCol>',
						COL_NAME(rkeyid, rkey)
					),
					'<@cTable>',
					OBJECT_NAME(fkeyid)
				),
				'<@cCol>',
				COL_NAME(fkeyid, fkey)
			)
	FROM	dbo.sysforeignkeys fk
	WHERE	fk.constid = @fkNameId --OBJECT_ID('FK_ProductArrearsMe_ProductArrears')
	
	RETURN LEFT(@str, LEN(@str) - LEN(' AND ') )
END
GO












IF OBJECT_ID('dbo.uspCascadeDelete') IS NOT NULL DROP PROCEDURE dbo.uspCascadeDelete
GO

CREATE PROCEDURE dbo.uspCascadeDelete
 	@ParentTableId VARCHAR(300), -- can also take object_id!!!
 	@WhereClause VARCHAR(2000),
	@ExecuteDelete CHAR(1) = 'N',
 	@FromClause VARCHAR(8000) = '',
	@Level INTEGER = 0
AS

-- writen by Daniel Crowther 16 Dec 2004 - handles composite primary keys

SET NOCOUNT ON

/* Set up debug */
DECLARE	@DebugMsg VARCHAR(4000),
	@DebugIndent VARCHAR(50)

SET @DebugIndent = REPLICATE('---', @@NESTLEVEL) + '> ' 

IF ISNUMERIC(@ParentTableId) = 0
BEGIN -- assume owner is dbo and calculate id
	IF CHARINDEX('dbo.', @ParentTableId) = 0
		SET @ParentTableId = OBJECT_ID('[dbo].[' + @ParentTableId + ']')
	ELSE
		SET @ParentTableId = OBJECT_ID(@ParentTableId)
END

IF @Level = 0
BEGIN
	PRINT @DebugIndent + ' **************************************************************************'
	PRINT @DebugIndent + ' *** Cascade delete ALL data from ' + dbo.udfGetFullQualName(@ParentTableId)
	IF @ExecuteDelete = 'Y'
		PRINT @DebugIndent + ' *** @ExecuteDelete = Y *** deleting data...'
	ELSE
		PRINT @DebugIndent + ' *** Cut and paste output into another window and execute ***'
END



DECLARE @CRLF CHAR(2)
SET @CRLF = CHAR(13) + CHAR(10)

DECLARE @strSQL VARCHAR(4000)

IF @Level = 0 SET @strSQL = 'SET NOCOUNT ON' + @CRLF
ELSE SET @strSQL = ''

SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST(@@NESTLEVEL AS VARCHAR) + ''''
IF @ExecuteDelete = 'Y' EXEC ( @strSQL )
ELSE PRINT @strSQL

DECLARE curs_children CURSOR LOCAL FORWARD_ONLY
FOR	SELECT DISTINCT
		fkNameId = constid,	-- constraint name
		cTableId = fkeyid	-- child table
/*		cColId = fkey,		-- child table column
		pTableId = rkeyid,	-- parent table
		pColId = rkey		-- parent table column
*/	FROM	dbo.sysforeignkeys fk
	WHERE	fk.rkeyid <> fk.fkeyid -- WE DO NOT HANDLE self referencing tables!!!
		AND
		fk.rkeyid = @ParentTableId

OPEN curs_children 

DECLARE @fkNameId INTEGER, 
	@cTableId INTEGER,
	@cColId INTEGER,
	@pTableId INTEGER,
	@pColId INTEGER

FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId --, @cColId, @pTableId, @pColId

DECLARE @strFromClause VARCHAR(1000)
DECLARE @nLevel INTEGER

IF @Level = 0
BEGIN
	SET @FromClause = 
		'FROM	' + dbo.udfGetFullQualName(@ParentTableId)
END

WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @strFromClause = 
			@FromClause + @CRLF + 
			'	INNER JOIN ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + 
			'		ON ' + dbo.udfGetOnJoinClause(@fkNameId)
	SET @nLevel = @Level + 1
	EXEC dbo.uspCascadeDelete
		@ParentTableId = @cTableId,
		@WhereClause = @WhereClause,
		@ExecuteDelete = @ExecuteDelete,
		@FromClause = @strFromClause,
		@Level = @nLevel

	SET @strSQL =  
		'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + 
		@strFromClause + @CRLF + 
		'WHERE	' + @WhereClause + @CRLF
	SET @strSQL = @strSQL + 'PRINT ''---' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + '	Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF + @CRLF
	
	IF @ExecuteDelete = 'Y' EXEC ( @strSQL )
	ELSE PRINT @strSQL

	FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId --, @cColId, @pTableId, @pColId
END

IF @Level = 0
BEGIN
	SET @strSQL = @CRLF + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST(@@NESTLEVEL AS VARCHAR) + ' TOP LEVEL PARENT TABLE''' + @CRLF
	SET @strSQL = @strSQL + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' WHERE ' + @WhereClause + @CRLF
	SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF

	IF @ExecuteDelete = 'Y' EXEC ( @strSQL )
	ELSE PRINT @strSQL
END

CLOSE curs_children 
DEALLOCATE curs_children 


GO

/*
-- Example 1
EXEC uspCascadeDelete
	@ParentTableId = 'dbo.ProductClass',
	@WhereClause = 'ProductClass.Class = ''RTB2'''
--	,@ExecuteDelete = 'Y'





-- Example 2
EXEC uspCascadeDelete
	@ParentTableId = 'dbo.brand',
	@WhereClause = 'brand.brand_name <> ''Apple'''
--	,@ExecuteDelete = 'Y'

exec uspCascadeDelete
	@ParentTableId = 'dbo.product_type',
	@WhereClause = 'product_type.product_type_id NOT IN (SELECT bpt.product_type_id FROM dbo.brand_product_type bpt)'
--	,@ExecuteDelete = 'Y'





*/






Thanks,

Daniel Crowther
Go to Top of Page

daniel_crowther
Starting Member

United Kingdom
2 Posts

Posted - 12/17/2004 :  09:38:42  Show Profile  Reply with Quote
BTW. My solution handles composite foreign key references - I knew there was something good about it!!!

Enjoy!



Thanks,

Daniel Crowther
Go to Top of Page

Gooser187
Starting Member

USA
1 Posts

Posted - 12/09/2009 :  14:43:56  Show Profile  Reply with Quote
quote:
Originally posted by GreySky

I greatly appreciated your code on the SQL cascade delete, and indeed it works as advertised. Unfortunately, I found that if the cascade tree was deep, it took a considerable amount of time to perform the cascade...


Tried this. Got:

Msg 217, Level 16, State 1, Procedure spCascadeDeleteLong, Line 55
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).


v/r
Gooser

http://www.CafePress.com/MerryCHRISTmass
Go to Top of Page

foobar11
Starting Member

2 Posts

Posted - 03/30/2010 :  13:27:41  Show Profile  Reply with Quote
GreySky,

thanks, your solution really got me going (the original one might fail due to too many nested subqueries). I added a "tablename"-column to the temptable, so cycles can be detected when setting the child-criteria (a simple subquery checking whether the current row has already been picked up on a lower call-level). Also, by casting the pk value to varchar (instead of int) I was able to mix all kinds of primary key types (guids, etc).
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000