Last active
September 16, 2017 03:05
-
-
Save chrisRedwine/06de18aef69fa42d388c to your computer and use it in GitHub Desktop.
T-SQL Stored Procedure Template from MSDN (with comments)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --Procedure: schema_name.stored_procedure_name --<<EDIT stored_procedure_name | |
| CREATE PROCEDURE schema_name.stored_procedure_name --<<EDIT stored_procedure_name | |
| /** | |
| * @ParameterOne DATATYPE --<<EDIT parameters | |
| * ,@ParameterTwo DATATYPE | |
| * ,... | |
| **/ | |
| AS | |
| --Configure pertinent session options | |
| SET XACT_ABORT OFF; | |
| SET NOCOUNT ON; | |
| /** | |
| * Detect whether the procedure was called | |
| * from an active transaction and save | |
| * that for later use. | |
| * In the procedure, @TranCounter = 0 | |
| * means there was no active transaction | |
| * and the procedure started one. | |
| * @TranCounter > 0 means an active | |
| * transaction was started before the | |
| * procedure was called. | |
| **/ | |
| DECLARE @TranCounter INTEGER = @@TRANCOUNT; | |
| IF @TranCounter > 0 | |
| /** | |
| * Procedure called when there is | |
| * an active transaction. | |
| * Create a savepoint to be able | |
| * to roll back only the work done | |
| * in the procedure if there is an | |
| * error. | |
| **/ | |
| SAVE TRANSACTION stored_procedure_name_savepoint; | |
| ELSE | |
| /** | |
| * Procedure must start its own | |
| * transaction. | |
| **/ | |
| BEGIN TRANSACTION; | |
| /* Modify database. */ | |
| BEGIN TRY | |
| /********************************************************/ | |
| /*******************ADD CODE HERE************************/ | |
| /********************************************************/ | |
| /** | |
| * Get here if no errors; must commit | |
| * any transaction started in the | |
| * procedure, but not commit a transaction | |
| * started before the transaction was called. | |
| **/ | |
| IF @TranCounter = 0 | |
| /** | |
| * @TranCounter = 0 means no transaction was | |
| * started before the procedure was called. | |
| * The procedure must commit the transaction | |
| * it started. | |
| **/ | |
| COMMIT TRANSACTION; | |
| END TRY | |
| BEGIN CATCH | |
| /** | |
| * An error occurred; must determine | |
| * which type of rollback will roll | |
| * back only the work done in the | |
| * procedure. | |
| **/ | |
| IF @TranCounter = 0 | |
| /** | |
| * Transaction started in procedure. | |
| * Roll back complete transaction. | |
| **/ | |
| ROLLBACK TRANSACTION; | |
| ELSE | |
| /** | |
| * Transaction started before procedure | |
| * called, do not roll back modifications | |
| * made before the procedure was called. | |
| **/ | |
| IF XACT_STATE() <> -1 | |
| /** | |
| * If the transaction is still valid, just | |
| * roll back to the savepoint set at the | |
| * start of the stored procedure. | |
| **/ | |
| ROLLBACK TRANSACTION excptn_reason_upsrt_savepoint; | |
| /** | |
| * If the transaction is uncommitable, a | |
| * rollback to the savepoint is not allowed | |
| * because the savepoint rollback writes to | |
| * the log. Just return to the caller, which | |
| * should roll back the outer transaction. | |
| **/ | |
| /** | |
| * After the appropriate rollback, echo error | |
| * information to the caller. | |
| **/ | |
| DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(); | |
| DECLARE @ErrorSeverity INT = ERROR_SEVERITY(); | |
| DECLARE @ErrorState INT = ERROR_STATE(); | |
| RAISERROR ( | |
| @ErrorMessage -- Message text. | |
| ,@ErrorSeverity -- Severity. | |
| ,@ErrorState -- State. | |
| ); | |
| END CATCH; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment