I'm writing a script that will delete records from a number of tables, but before it deletes it must return a count for a user to confirm before committing.
This is a summary of the script.
BEGIN TRANSACTION SCHEDULEDELETE
BEGIN TRY
DELETE -- delete commands full SQL cut out
DELETE -- delete commands full SQL cut out
DELETE -- delete commands full SQL cut out
PRINT 'X rows deleted. Please commit or rollback.' --calculation cut out.
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
ROLLBACK TRANSACTION SCHEDULEDELETE
PRINT 'Error detected, all changes reversed.'
END CATCH
--COMMIT TRANSACTION SCHEDULEDELETE --Run this if count correct.
--ROLLBACK TRANSACTION SCHEDULEDELETE --Run this if there is any doubt whatsoever.
This is my first time writing transaction, is it correct/best practice to have the TRY/CATCH block inside the transaction or should the transaction be inside the TRY block?
The important factor in this script is that the user must manually commit the transaction.
See Question&Answers more detail:os