// 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
}

-- 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; 

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

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

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;

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

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(){}
} 

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;

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

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