quote:

---

Originally posted by arkiboys

Hi,
Can you explain what Go does in sql?
It seems if you do not have GO in between your sql in query analyser, then everything is inside a transaction.
Am I right?
Thanks

---

quote:

---

Originally posted by arkiboys

Hi,
Can you explain what Go does in sql?
It seems if you do not have GO in between your sql in query analyser, then everything is inside a transaction.
Am I right?
Thanks

---

BEGIN TRANSACTION
GO
.... Do some stuff - within the transaction ...
GO
... Do some other stuff - also within the transaction ...
... BUT ... This "stuff" has a runtime error ...
GO
-- At this point you can safely ROLLBACK

... Do some MORE  stuff
... At THIS point an implicit Rollback is called for all the previous statements in this batch
... and THEN the statements in this block are executed OUTSIDE THE TRANSACTION
GO
-- COMMIT or ROLLBACK will fail at this point

CREATE TABLE t1 (id INT PRIMARY KEY);

BEGIN TRANSACTION
GO
CREATE TABLE t2 (id INT PRIMARY KEY);
GO

INSERT INTO t1 VALUES (1)

GO

INSERT INTO t1 VALUES (1) -- this will fail. Primary key violation

GO

INSERT INTO t1 VALUES (2)
INSERT INTO t1 VALUES (3)
INSERT INTO t2 VALUES (1)

GO

ROLLBACK

SELECT * FROM t1 -- 0 rows returned
go
SELECT * FROM t2 -- error, table creation was rolled back
go

CREATE TABLE #tmpErrors (Error int)
GO
BEGIN TRANSACTION
GO
... attempt a statement ...
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
... repeat for other statements ...
-- At end:
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO

COMMIT -- Next step must be outside a transaction
GO

IF EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id(N'[dbo].[SomeTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT '***** "SomeTable" ALREADY EXISTS - DO NOT PROCESS THE NEXT "CREATE TABLE" SECTION *****'
	RAISERROR(
	    N'*** MANUAL INTERVENTION REQUIRED(1) - NOTE ANY MESSAGES DISPLAYED AND THEN ABORT SCRIPT AND RESUME MANUALLY ***',
	    10, 1) WITH NOWAIT
	RAISERROR (N'*** PRESS CANCEL NOW !!! ***', 10, 1) WITH NOWAIT
	WAITFOR DELAY '01:00:00'
END
GO

BEGIN TRANSACTION -- Start transaction if the script continues
GO