Deleting data in a database can a very dangerous operation in the wrong hands. Sometimes you are too busy to really concentrate fully; or sometimes you have given the wrong person DELETE permissions. If the wrong data is deleted, you can have a major problem (although “problem” is not really the correct word here).

You must always ensure that you have a proper backup of your database at all times. You must also always ensure that you make use of Transactions to do any data manipulation. Here is a small sample of a Delete Stored Procedure with the help of Transactions:

CREATE PROCEDURE sp_Delete(@TableToDelete SYSNAME, @WhereCondition VARCHAR(MAX), @DelCount BIGINT, @ActionCount BIGINT OUTPUT)ASBEGINDECLARE @sql VARCHAR(MAX)BEGIN TRANSACTIONSELECT @sql = 'DELETE FROM ' + @TableToDelete + ' ' + @WhereConditionEXECUTE(@sql)SELECT @ActionCount = @@ROWCOUNTIF @ActionCount = @DelCountBEGINPRINT CAST(@ActionCount AS VARCHAR) + ' rows deleted.'COMMIT TRANSACTIONENDELSEBEGINPRINT 'Statement would have deleted ' + CAST(@ActionCount AS VARCHAR)ROLLBACK TRANSACTIONSELECT @ActionCount = 0ENDEND