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)
AS
BEGIN
DECLARE @sql VARCHAR(MAX)
BEGIN TRANSACTION
SELECT @sql = 'DELETE FROM ' + @TableToDelete + ' ' + @WhereCondition
EXECUTE(@sql)
SELECT @ActionCount = @@ROWCOUNT
IF @ActionCount = @DelCount
BEGIN
PRINT CAST(@ActionCount AS VARCHAR) + ' rows deleted.'
COMMIT TRANSACTION
END
ELSE
BEGIN
PRINT 'Statement would have deleted ' + CAST(@ActionCount AS VARCHAR)
ROLLBACK TRANSACTION
SELECT @ActionCount = 0
END
END
Visit the DevX Tip Bank
