none
Stored Proc to delete data from multiple tables

    Question

  • Hi

    Am trying to delete data from multiple tables.

    DELETE FROM table1

    DELETE FROM table2

    DELETE FROM table3

    DELETE FROM table4

    How can I do this using a stored proc?

    Thanks

    Monday, January 10, 2011 10:48 PM

Answers

  • Do you want to delete related records? If so, you better set CASCADE for DELETE.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Tuesday, January 18, 2011 9:45 AM
    Monday, January 10, 2011 11:36 PM
    Moderator

All replies

  • You probably need to wrap all these statements into one transaction. In SQL 2005+ it will be something like this

     

    create procedure DeleteFromMultipleTables
    AS
    BEGIN
    begin try
    
       begin transaction
    
          DELETE FROM table1
    
    
       DELETE FROM table2
    
       DELETE FROM table3
    
       DELETE FROM table4
    
      commit transaction
    
    END TRY
                BEGIN
                    CATCH
                    DECLARE @ErrorSeverity INT,
                        @ErrorNumber  INT,
                        @ErrorMessage NVARCHAR(4000),
                        @ErrorState INT,
                        @ErrorLine INT,
                        @ErrorProc NVARCHAR(200)
                        -- Grab error information from SQL functions
                    SET @ErrorSeverity = ERROR_SEVERITY()
                    SET @ErrorNumber  = ERROR_NUMBER()
                    SET @ErrorMessage = ERROR_MESSAGE()
                    SET @ErrorState  = ERROR_STATE()
                    SET @ErrorLine   = ERROR_LINE()
                    SET @ErrorProc   = ERROR_PROCEDURE()
                    SET @ErrorMessage = 'Problem updating person''s information.' + CHAR(13) + 'SQL Server Error Message is: ' + CAST(@ErrorNumber AS VARCHAR(10)) + ' in procedure: ' + @ErrorProc + ' Line: ' + CAST(@ErrorLine AS VARCHAR(10)) + ' Error text: ' + @ErrorMessage
                    -- Not all errors generate an error state, to set to 1 if it's zero
                    IF @ErrorState = 0
                    SET @ErrorState = 1
                    -- If the error renders the transaction as uncommittable or we have open transactions, we may want to rollback
                    IF @@TRANCOUNT > 0
                    BEGIN
                        --print 'Rollback transaction'
                        ROLLBACK TRANSACTION
                    END
                    RAISERROR (@ErrorMessage , @ErrorSeverity, @ErrorState, @ErrorNumber)
                END CATCH
                RETURN @@ERROR
            END
            GO
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, January 10, 2011 10:56 PM
    Moderator
  • Hey,

    Thanks for the reply. Right now I am doing something like (If I was to write a stored proc to delete data from just one table)

    CREATE PROCEDURE deletedatafromtable1

    @table1ID

    int = NULL

    AS

    BEGIN

    DECLARE

     

    @rowstodelete table(table1ID int);

    insert

     

    into @rowstodelete(table1ID)

    select

     

    table1ID from table1

    where

     

    table1ID = @table1ID

    delete

     

    from table1

    where

     

    table1ID in (select table1ID from @rowstodelete)

    END

    GO

    However, to extend this stored proc to delete data from 3 more tables like the above table is my problem (all in one store proc). How do I declare a single variable and use it to hold all the id values from 4 tables.

    Thanks

    Monday, January 10, 2011 11:26 PM
  • Do you want to delete related records? If so, you better set CASCADE for DELETE.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Tuesday, January 18, 2011 9:45 AM
    Monday, January 10, 2011 11:36 PM
    Moderator