devxlogo

Delete Data Safely with SQL

Delete Data Safely with SQL

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
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist