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


advertisement
 

Tip: A Handy Delete Stored Procedure

Ensure that your data is safe when you make use of Transactions to do any data manipulation with this sample stored procedure.


advertisement

WEBINAR:

On-Demand

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


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

 





   
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date