dcsimg
 

Submitted by: Hannes du Preez(ojdupreez1978@gmail.com)

Thursday Mar 1st 2018 by Hannes du Preez

Language: SQL, Expertise: Intermediate - Ensure that your data is safe when you make use of Transactions to do any data manipulation with this sample stored procedure.

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
Home
Mobile Site | Full Site
Copyright 2018 © QuinStreet Inc. All Rights Reserved