WEBINAR:
On-Demand
Building the Right Environment to Support AI, Machine Learning and Deep Learning
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
Visit the DevX Tip Bank
