You never know what can happen when you attempt to delete data. Some may argue that it is best to delete data through a client application, some may say it is better to do so through SQL Server itself, by using SQL code. Here is an example on how to always safely delete your data in SQL:
CREATE PROCEDURE sp_DeleteSafely (@Table SYSNAME, @Clause VARCHAR(MAX), @DeleteCount BIGINT, @ActionCount BIGINT OUTPUT) ASBEGINDECLARE @sql VARCHAR(MAX)BEGIN TRANSACTIONSELECT @sql = 'DELETE FROM ' + @Table + ' ' + @ClauseEXECUTE(@sql)SELECT @ActionCount = @@ROWCOUNT IF @ActionCount = @DeleteCount BEGIN PRINT CAST(@ActionCount AS VARCHAR) + ' Rows Deleted.' COMMIT TRANSACTION END ELSE BEGIN PRINT 'Would Have Deleted ' + CAST(@ActionCount AS VARCHAR) + ROLLBACK TRANSACTION SELECT @actcnt = 0 ENDENDGO
Usage:
DECLARE @ActionCount BIGINTEXEC sp_DeleteSafely 'TableName','WHERE CONDITION', 1, @ActionCount OUTPUT
Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
