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