Skip to content

Instantly share code, notes, and snippets.

@binki
Created March 28, 2017 17:52
Show Gist options
  • Save binki/d7d597a1732a385809b4db824c057986 to your computer and use it in GitHub Desktop.
Save binki/d7d597a1732a385809b4db824c057986 to your computer and use it in GitHub Desktop.
Why doesn’t @@TRANCOUNT increment after my IF statement?
(1 row(s) affected)
unreachable
after1 0
after2 0
after3 1
SET IMPLICIT_TRANSACTIONS ON
GO
IF OBJECT_ID('dbo.t1') IS NOT NULL
BEGIN
DROP TABLE dbo.t1
COMMIT
END
GO
CREATE TABLE dbo.t1 (x INT);
GO
INSERT INTO dbo.t1 VALUES (1);
COMMIT;
GO
IF CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END = 1 PRINT 'unreachable';
PRINT 'after1 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after1 0
DECLARE @x INT = 2;
PRINT 'after2 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after2 0
DECLARE @y INT = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;
PRINT 'after3 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after3 1
GO
WHILE @@TRANCOUNT > 0 ROLLBACK
GO
@binki
Copy link
Author

binki commented Mar 28, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment