This example uses Tx methods that take a context.Context argument. This makes it possible for the function’s execution – including database operations – to be canceled if it runs too long or the client connection closes. For more, see Canceling in-progress operations.

// CreateOrder creates an order for an album and returns the new order ID.
func CreateOrder(ctx context.Context, albumID, quantity, custID int) (orderID int64, err error) {

    // Create a helper function for preparing failure results.
    fail := func(err error) (int64, error) {
        return fmt.Errorf("CreateOrder: %v", err)
    }

    // Get a Tx for making transaction requests.
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return fail(err)
    }
    // Defer a rollback in case anything fails.
    defer tx.Rollback()

    // Confirm that album inventory is enough for the order.
    var enough bool
    if err = tx.QueryRowContext(ctx, "SELECT (quantity >= ?) from album where id = ?",
        quantity, albumID).Scan(&enough); err != nil {
        if err == sql.ErrNoRows {
            return fail(fmt.Errorf("no such album"))
        }
        return fail(err)
    }
    if !enough {
        return fail(fmt.Errorf("not enough inventory"))
    }

    // Update the album inventory to remove the quantity in the order.
    _, err = tx.ExecContext(ctx, "UPDATE album SET quantity = quantity - ? WHERE id = ?",
        quantity, albumID)
    if err != nil {
        return fail(err)
    }

    // Create a new row in the album_order table.
    result, err := tx.ExecContext(ctx, "INSERT INTO album_order (album_id, cust_id, quantity, date) VALUES (?, ?, ?, ?)",
        albumID, custID, quantity, time.Now())
    if err != nil {
        return fail(err)
    }
    // Get the ID of the order item just created.
    orderID, err := result.LastInsertId()
    if err != nil {
        return fail(err)
    }

    // Commit the transaction.
    if err = tx.Commit(); err != nil {
        return fail(err)
    }

    // Return the order ID.
    return orderID, nil
}

If you are using SQL 2005 or higher, you can use TRY...CATCH (Transact-SQL). Here's the example from the provided link:


-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
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;
GO

BEGIN TRY
    -- Generate divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    -- Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;
END CATCH; 

Example script:


SET XACT_ABORT ON; --ensures transaction is rolled back immediately even if script is cancelled
BEGIN TRY

    BEGIN TRAN;

    --truncate in same transaction so entire script can be safely rerun
    TRUNCATE TABLE dbo.MainStaging;

    --ALTER TABLE will block other activity until committed due to schema modification lock
    --main table will be empty after switch
    ALTER TABLE dbo.Main SWITCH TO dbo.MainStaging;

    --keep 5% of rows
    INSERT INTO dbo.Main WITH(TABLOCKX) (MainID, MainData)
    SELECT MainID, MainData
    FROM dbo.MainStaging
    WHERE MainID > 950000;

    COMMIT;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;

END CATCH;
GO

Example:


GO
  return
  SELECT 'Test 1'
GO
  SELECT 'Test 2'
GO

Example:


SET NOCOUNT ON;
SET IMPLICIT_TRANSACTIONS ON;

CREATE TABLE MyTable (MyID INT  PRIMARY KEY);
GO
INSERT  MyTable (MyID)
VALUES  (11), (22), (33), (44), (55);

PRINT 'Test MyCTE:';
WITH MyCTE
AS (
    SELECT  *, ROW_NUMBER()OVER(ORDER BY MyID) AS RowNum
    FROM    MyTable
)
SELECT  *
FROM    MyCTE crt
LEFT JOIN MyCTE prev ON crt.RowNum=prev.RowNum+1;

ROLLBACK;

As far as I know, you have to create a temporary table with the ID field created as IDENTITY, then copy all the data from the original table. Finally, you drop the original table and rename the temporary one. This is an example with a table (named TestTable) that contains only one field, called ID (integer, non IDENTITY):


BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TestTable
    (
    ID int NOT NULL IDENTITY (1, 1)
    )  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_TestTable ON
GO
IF EXISTS(SELECT * FROM dbo.TestTable)
     EXEC('INSERT INTO dbo.Tmp_TestTable (ID)
        SELECT ID FROM dbo.TestTable WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_TestTable OFF
GO
DROP TABLE dbo.TestTable
GO
EXECUTE sp_rename N'dbo.Tmp_TestTable', N'TestTable', 'OBJECT' 
GO
COMMIT

Modify transaction aspect as follows:


package com.example;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;

@Aspect
public class TxAspect {

    @Around("methodsToBeProfiled()")
    public void test(ProceedingJoinPoint pjp) throws Throwable {
        System.out.println("I am not going to do anything");
        pjp.proceed();
    }

    @Pointcut("execution(* com.example.ConcreteClass.abstractMethod(..))")
    public void methodsToBeProfiled(){}
} 

Example code pilfered shamelessly from the workaround by Paul White on this Microsoft Connect Item.


USE tempdb;
GO
-- A table with an identity column
CREATE TABLE dbo.Source 
(row_id INTEGER IDENTITY PRIMARY KEY NOT NULL, data SQL_VARIANT NULL);
GO
-- Some sample data
INSERT dbo.Source (data)
VALUES (CONVERT(SQL_VARIANT, 4)),
        (CONVERT(SQL_VARIANT, 'X')),
        (CONVERT(SQL_VARIANT, {d '2009-11-07'})),
        (CONVERT(SQL_VARIANT, N'áéíóú'));
GO
-- Remove the identity property
BEGIN TRY;
    -- All or nothing
    BEGIN TRANSACTION;

    -- A table with the same structure as the one with the identity column,
    -- but without the identity property
    CREATE TABLE dbo.Destination 
    (row_id INTEGER PRIMARY KEY NOT NULL, data SQL_VARIANT NULL);

    -- Metadata switch
    ALTER TABLE dbo.Source SWITCH TO dbo.Destination;

    -- Drop the old object, which now contains no data
    DROP TABLE dbo.Source;

    -- Rename the new object to make it look like the old one
    EXECUTE sp_rename N'dbo.Destination', N'Source', 'OBJECT';

    -- Success
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Bugger!
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    PRINT ERROR_MESSAGE();
END CATCH;
GO

-- Test the the identity property has indeed gone
INSERT dbo.Source (row_id, data)
VALUES (5, CONVERT(SQL_VARIANT, N'This works!'))

SELECT row_id,
        data
FROM    dbo.Source;
GO

-- Tidy up
DROP TABLE dbo.Source;

Try using GO statements between each execution block. For example:


USE msdb;
BEGIN TRANSACTION

DECLARE @JobName = 'MyJob'

/*blah blah blah*/

COMMIT TRANSACTION

GO

USE msdb;
BEGIN TRANSACTION

DECLARE @JobName = 'MySecondJob'

/*blah blah blah*/

COMMIT TRANSACTION

GO

Below is an example using the tsqlt unit test framework. The tSQLt.Run framework proc executes unit tests in a transaction, which is rolled back after the test. Although unit tests like this can be executed against a shared development database, I recommend running unit tests on an isolated database to avoid impacting other developers.


CREATE PROC dbo.test_dbo_update_player
AS

--create a stub view that returns known values for unit testing
EXEC sp_rename N'dbo.getRandomValue', 'getRandomValue_orig';
EXEC('CREATE VIEW dbo.getRandomValue AS SELECT 1 AS value;');

--create a fake table with known data for this test case
EXEC tSQLt.FakeTable 'dbo.Player';
INSERT INTO dbo.Player (UserName,Coins) Values(N'Test', 0);

EXEC dbo.update_player 
      @Username   = N'Test'
    , @Luck       = 2
    , @DiceNumber = 3;

DECLARE @actual int =(SELECT Coins FROM dbo.Player WHERE UserName = N'Test');
EXEC tSQLt.AssertEquals @expected = 6, @actual = @actual, @message = 'assert equal failed for test case';
GO

--unit test framework executes unit test in a transaction and will rollback after completion
EXEC tSQLt.Run @testName = 'dbo.test_dbo_update_player';
GO

Recommend

Go Querying for data Handling multiple result sets

Go Querying for data Handling nullable column values

Go Querying for data Querying for multiple rows

Go Querying for data Querying for a single row

Go Avoiding SQL injection risk

Go Using prepared statements How you use prepared statements

Go Opening a database handle Freeing resources

Go Opening a database handle Storing database credentials

Go Opening a database handle Confirming a connection

Go Opening a database handle Opening a database handle Opening with a Connector

Go Opening a database handle Opening a database handle Opening with a connection string

Go Opening a database handle Locating and importing a database driver

Go Call your code from another module

Go Compile and install the application

Go Return greetings for multiple people

Tutorial: Get started with Go Call code in an external package

Tutorial: Get started with Go Write some code

Go Tutorial: Getting started with fuzzing Completed code

Go Tutorial: Getting started with fuzzing Fix the double reverse error Fix the error Run the code

Go Tutorial: Getting started with fuzzing Fix the double reverse error Fix the error Write the code

Go Tutorial: Getting started with fuzzing Fix the double reverse error Diagnose the error Run the code

Go Tutorial: Getting started with fuzzing Fix the double reverse error Diagnose the error Write the code

Go Tutorial: Getting started with fuzzing Fix the invalid string error Fix the error Run the code

Go Tutorial: Getting started with fuzzing Fix the invalid string error Fix the error Write the code

Go Tutorial: Getting started with fuzzing Fix the invalid string error Diagnose the error Run the code

Go Tutorial: Getting started with fuzzing Fix the invalid string error Diagnose the error Write the code

Go Tutorial: Getting started with fuzzing Fix the invalid string error Diagnose the error

Go Tutorial: Getting started with fuzzing Add a fuzz test Run the code

Go Tutorial: Getting started with fuzzing Add a fuzz test Write the code

Go Tutorial: Getting started with fuzzing Add a unit test Run the code

Go Tutorial: Getting started with fuzzing Add a unit test Write the code

Go Tutorial: Getting started with fuzzing Add code to test Run the code

Go Tutorial: Getting started with fuzzing Add code to test Write the code

Go Tutorial: Getting started with fuzzing Create a folder for your code

Tutorial: Create a Go module Start a module that others can use

Go Add a test

Go Return and handle an error

Go Return a random greeting

Go Tutorial: Getting started with multi-module workspaces Download and modify the golang.org/x/example module Future step

Go Tutorial: Getting started with multi-module workspaces Download and modify the golang.org/x/example module Run the code in the workspace

Go Tutorial: Getting started with multi-module workspaces Download and modify the golang.org/x/example module