dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Sep 4, 2019

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


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
) AS
BEGIN

DECLARE @sql VARCHAR(MAX)

BEGIN TRANSACTION

SELECT @sql = 'DELETE FROM ' + @Table + ' ' + @Clause
EXECUTE(@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
	END
END
GO

Usage:

DECLARE @ActionCount BIGINT
EXEC sp_DeleteSafely 'TableName','WHERE CONDITION', 1, @ActionCount OUTPUT
Hannes du Preez
 
Thanks for your registration, follow us on our social networks to keep up-to-date