CREATE TABLE impl_txn_prepexec_table (a varchar(15) UNIQUE, b nvarchar(25), c int PRIMARY KEY, d char(15) DEFAULT 'Whoops!', e nchar(25), f datetime, g numeric(4,1) CHECK (g >= 103.5))

prepst#!#INSERT INTO impl_txn_prepexec_table VALUES (@a, @b, @c, @d, @e, @f, @g)#!#varchar|-|a|-|Apple#!#nvarchar|-|b|-|red#!#int|-|c|-|1#!#char|-|d|-|Delhi#!#nchar|-|e|-|Sad😞#!#datetime|-|f|-|2000-12-13 12:58:23.123#!#numeric|-|g|-|123.1|-|4|-|1

SET IMPLICIT_TRANSACTIONS ON

SELECT @@TRANCOUNT
prepst#!#SELECT * FROM impl_txn_prepexec_table WHERE a = @p1 AND b = @p2 AND c = @p3#!#varchar|-|p1|-|Apple#!#nvarchar|-|p2|-|red#!#int|-|p3|-|1
SELECT @@TRANCOUNT
IF @@TRANCOUNT > 0 COMMIT

SELECT @@TRANCOUNT
prepst#!#exec#!#varchar|-|p1|-|Apple#!#nvarchar|-|p2|-|red#!#int|-|p3|-|2
SELECT @@TRANCOUNT
IF @@TRANCOUNT > 0 COMMIT

SELECT @@TRANCOUNT
prepst#!#exec#!#varchar|-|p1|-|Apple#!#nvarchar|-|p2|-|green#!#int|-|p3|-|1
SELECT @@TRANCOUNT
IF @@TRANCOUNT > 0 COMMIT

SELECT @@TRANCOUNT
prepst#!#SELECT 12, 34, 56 FROM (SELECT * FROM impl_txn_prepexec_table WHERE a = @p1 AND b = @p2 AND c = @p3) AS dummy_table#!#varchar|-|p1|-|Apple#!#nvarchar|-|p2|-|red#!#int|-|p3|-|1
SELECT @@TRANCOUNT
IF @@TRANCOUNT > 0 COMMIT

SELECT @@TRANCOUNT
prepst#!#exec#!#varchar|-|p1|-|Apple#!#nvarchar|-|p2|-|red#!#int|-|p3|-|2
SELECT @@TRANCOUNT
IF @@TRANCOUNT > 0 COMMIT

SELECT @@TRANCOUNT
prepst#!#exec#!#varchar|-|p1|-|Apple#!#nvarchar|-|p2|-|green#!#int|-|p3|-|1
SELECT @@TRANCOUNT
IF @@TRANCOUNT > 0 COMMIT

SET IMPLICIT_TRANSACTIONS OFF
DROP TABLE impl_txn_prepexec_table
