Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    KY
    Posts
    56

    Angry Unanswered: Stored Procedure that deletes from multiple tables

    I'm trying to create a script that will delete all records from multple tables that I can use over and over again as a store procedure.
    Here's and example of what I have:
    Am i on the right path??????

    CREATE PROCEDURE sp_Delete_Main_Blr_Info_Links
    (
    @Cont_ID int,
    @Blr_Insp_ID int,
    @Blr_ID int,
    @Cont_TypeDef_ID int

    )

    AS

    Delete From HBC_Boiler_Inspection_Contact_Link;

    Delete From HBC_Violation_Boiler_Inspection_Link Where Boiler_Inspection_ID = @Boiler_Inspection_ID;

    Delete From HBC_License_Holder Where Contact_ID = @Contact_ID;

    Delete From HBC_Boiler_Inspection Where Boiler_ID = @Boiler_ID;

    Delete From HBC_Contact_Contact_TypeDef_Link Where Contact_ID = @Contact_ID and Contact_TypeDef_ID = @Contact_TypeDef_ID;

    Delete From HBC_Boiler

  2. #2
    Join Date
    Jul 2003
    Posts
    21

    Re: Stored Procedure that deletes from multiple tables

    Your code looks fine,
    when deleteing complete tables you should consider
    using TRUNCATE TABLE instead of DELETE
    its much faster, however,this will also initialize the
    auto number record count and keys so if you have all sorts of restrictions this may not be such a hot idea.

    read about it in books online to see if it fits you.

    LDBA

  3. #3
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    truncating tables also doesn't get recorded in the log files
    Johan

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...so your database can't be reliably restored to a point in time after the truncate. If you want recoverability you would need to run a database backup, and that negates the speed advantage of TRUNCATE. It's fine for a development environment, but not for a production system.

    Why aren't you using defined referential integrity with automatic cascading of updates and deletes?

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •