2

I have example like this,

Table1
Table2
Table21
Table22
Table23

Table2 is child table of Table1

I had wrote a stored procedure Delete_Table2 which deletes all child tables, Table21, Table22, Table23 and Table2 also.

Now I am creating delete stored procedure for deleting record from Table1 like:

Create PROC [dbo].[Delete_Table1]
    @Table1Id int
AS
BEGIN
    BEGIN TRAN delete_table1_trans
        --Select Table2ID from Table2 where Table1Id = @Table1ID
        --loop records and EXEC Delete_Table2 @param

    COMMIT TRAN delete_table1_trans
END

What will be the best idea? I can use a cursor, but trying to find out good idea like, calling function on select statement.

Select DELETE_Table2 (Table2Id) 
from Table2 
where Table1Id = 1
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mujah Maskey
  • 8,654
  • 8
  • 40
  • 61
  • why you will want to delete so many tables ?what is the real story like ? Why don't you put everything under one proc along with transaction ?You can get best IDEA only when requirement is little clear . – KumarHarsh Jan 08 '15 at 08:48

1 Answers1

0

If the tables are set up properly ON CASCADE DELETE will do this for you. What you describe seems like abuse of the RDBMS IMO. If the key relationships are that obtuse I'd recommend considering another design.

How do I use cascade delete with SQL Server?

Community
  • 1
  • 1
cjohnso55
  • 1
  • 1