Patrick Ge Code

DBCC FREEPROCCACHE;
USE tempdb;
GO
CREATE PROCEDURE dbo.Demo
AS
BEGIN
    CREATE TABLE #Demo (i int);

    SELECT
        t.name,
        t.object_id,
        t.type_desc,
        t.create_date
    FROM
        sys.tables AS t
    WHERE
        t.name LIKE N'#Demo%';

    IF EXISTS
    (
        SELECT *
        FROM #Demo
        WHERE i = 1
    )
    BEGIN
        --NEVER HAPPENS
        BEGIN TRANSACTION;

        UPDATE #Demo
        SET i = i + 1;

        WAITFOR DELAY '00:00:05';

        COMMIT TRANSACTION;
    END
    ELSE
    BEGIN
        --BEGIN TRAN
        INSERT  INTO #Demo
        VALUES (1)
        --WAITFOR DELAY '00:00:05';
        --COMMIT
    END;

    SELECT *
    FROM #Demo;

    DROP TABLE #Demo;

    SELECT
        t.name,
        t.object_id,
        t.type_desc,
        t.create_date
    FROM
        sys.tables AS t
    WHERE
        t.name LIKE N'#________';
END;
GO
--DBCC FREEPROCCACHE;
--WAITFOR DELAY '00:00:05';
--GO
EXECUTE dbo.Demo;
GO
--DBCC FREEPROCCACHE;
--WAITFOR DELAY '00:00:05';
--GO
EXECUTE dbo.Demo;
GO