Knowledge Base

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

====== SQL Transaction Template ======

<code sql>
BEGIN TRY
   BEGIN TRANSACTION

   -- Do stuff

   COMMIT
END TRY
BEGIN CATCH
   PRINT '[ERROR] ' + ERROR_MESSAGE();
   PRINT '[SEVERITY] ' + CAST(ERROR_SEVERITY() AS VARCHAR);
   PRINT '[STATE] ' + CAST(ERROR_STATE() AS VARCHAR);

   ROLLBACK
END CATCH
</code>

===== Safe Way to Test =====

<code sql>
BEGIN TRY
   BEGIN TRANSACTION

   -- 1. SELECT statement to check the "pre" state.

   -- 2. UPDATE to make the change.

   -- 3. SELECT statement to check the "post" state.

   ROLLBACK -- Change back to a COMMIT when you're ready for production.
END TRY
BEGIN CATCH
   PRINT '[ERROR] ' + ERROR_MESSAGE();
   PRINT '[SEVERITY] ' + CAST(ERROR_SEVERITY() AS VARCHAR);
   PRINT '[STATE] ' + CAST(ERROR_STATE() AS VARCHAR);

   ROLLBACK
END CATCH
</code>

{{tag>database}}