Need Cascade Delete like Stored Procedure

Question

Need Cascade Delete like Stored Procedure

francissvk

SSChasing Mays

Points: 620
More actions
March 25, 2011 at 7:01 am

#236735

Hi Gurus,
I require to delete the data from reference tables (child tables) and then delete from parent table, A stored Procedure that where I need to pass the table name and criteria. Find all the reference columns and reference tables, then delete the data from the reference tables based on the Criteria passed and then finally delete data from the tablename (that is "Master" table) passed
I have NOT used on delete cascade option in my DB.
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

You'd have to write something that uses sys.foreign_keys and sys.foreign_key_columns to find all the child tables, build delete statements with dynamic SQL and loop through them to delete.

It gets more difficult when you have multiple inter-dependencies within the child tables themselves as well, so it's not always straight forward.

francissvk SSChasing Mays Points: 620 More actions · Answer 2

Hi,

Thanks for your reply. Can u give any code samples? That could help me to go further.

Thanks,

Lowell SSC Guru Points: 323518 More actions · Answer 3

very similar to your other thread; not sure why you want to dynamically delete stuff, but here's an ugly example.

right ow, this is only printing the commands it might execute; you'll have to uncomment out both EXEC commands;

also, if there is a granchild FK reference,this will rollback the transaction and no delete would occur.

--EXEC DynamicDelete 'TBBreakfast',' WHERE MainCourse = ''cereal'' '

CREATE PROCEDURE DynamicDelete

(@ParentTbName sysname,

@Criteria VARCHAR(500)

)

AS

BEGIN --PROC

--if any error occurs, rollback the whole DELETE stack

SET XACT_ABORT ON

--we assume this is something dynamic but including the WHERE like 'WHERE SOMEKEY = 1 AND STATUS = 'Locked'

create table #tmpFKeys

(PKTABLE_QUALIFIER sysname not null,

PKTABLE_OWNER sysname not null,

PKTABLE_NAME sysname not null,

PKCOLUMN_NAME sysname not null,

FKTABLE_QUALIFIER sysname not null,

FKTABLE_OWNER sysname not null,

FKTABLE_NAME sysname not null,

FKCOLUMN_NAME sysname not null,

KEY_SEQ smallint not null,

UPDATE_RULE smallint not null,

DELETE_RULE smallint not null,

FK_NAME sysname not null,

PK_NAME sysname not null,

DEFERRABILITY int not null)

Create index #tmpFKeys on #tmpFKeys (FK_NAME, KEY_SEQ)

-- Get FK-info (all dependant objects)

insert into #tmpFKeys

exec sp_fkeys @pktable_name = @ParentTbName

-- select * from #tmpFKeys

BEGIN TRAN

declare

@isql varchar(2000),

@chldTbl varchar(64),

@ChildCol varchar(64),

@ParentCol varchar(64)

declare c1 cursor for select FKTABLE_NAME,FKCOLUMN_NAME,PKCOLUMN_NAME from #tmpFKeys

open c1

fetch next from c1 into @chldTbl,@ChildCol,@ParentCol

While @@fetch_status <> -1

begin

select @isql = ' DELETE FROM ' + @chldTbl + ' WHERE ' + @ChildCol + ' IN (SELECT ' + @ParentCol + ' FROM ' + @ParentTbName + ' ' + @Criteria + ')'

print @isql

--exec(@isql) commented out for now

fetch next from c1 into @chldTbl,@ChildCol,@ParentCol

end

close c1

deallocate c1

--now the master table itself

select @isql = ' DELETE FROM ' + @ParentCol + ' FROM ' + @ParentTbName + ' ' + @Criteria

print @isql

--exec(@isql) commented out for now

COMMIT TRAN

END --PROC

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

francissvk SSChasing Mays Points: 620 More actions · Answer 4

Hi Lowell,

thanks for your reply. I want to go all levels. but ur proc will go still Grand child level. Can u have any other idea, regarding this issue?

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 5

francissvk (3/29/2011)
Hi Lowell,
thanks for your reply. I want to go all levels. but ur proc will go still Grand child level. Can u have any other idea, regarding this issue?
Thanks

ideas for what? your two threads are asking two different questions; can you give a better explanation of what you want, and provide some example data that shows it?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!