Half of a transaction completes, when the other half fails?

I have done some searching on SO, MSDN, and various other sources, and I found plenty of questions about transactions, but none that seem to be exactly what I'm dealing with.

Admittedly I'm still very much a junior DBA, though I do understand the concept of ACID and transactions. I'm working on a SQL script that:

• Adds a row to 2 different tables, provided the values aren't already

there

• Drops a column if it exists

• Adds a column if it doesn't exist

• Alters 2 stored procedures that generate dynamic SQL to build a report query

All of the above operations work on their own, and they also work together when wrapped up in (what I believe to be) a single transaction. What I'm trying to understand is how "part" of the transaction completes (and presumably commits) when another part of it fails.

For the sake of brevity, assume I have working procedures called "ProcessReport" and "RetrieveReport." The dynamic SQL in both of these procedures starts with "SELECT" and then builds from there. Test cases:

CREATE TABLE ReportTable (FirstName VARCHAR(100), LastName VARCHAR(100), OrderId INT);
GO
CREATE PROCEDURE ProcessReport
AS
BEGIN
  DECLARE @SQL VARCHAR(4000)

  -- start creating dynamic sql   
  SET @SQL = 'SELECT FirstName, LastName, '
  --create rest of dynamic SQL here...

  INSERT INTO ReportTable (FirstName, LastName) EXEC(@SQL)
END
GO 

CREATE PROCEDURE RetrieveReport
AS
BEGIN
  DECLARE @SQL VARCHAR(4000)

  -- start creating dynamic sql   
  SET @SQL = 'SELECT FirstName, LastName '
  --create rest of dynamic SQL here...

  SET @SQL = @SQL + ' FROM ReportTable '
  EXEC (@SQL)
END
GO

Now that the procedures exist, here's what has me kerfuffled: when I purposely enter the wrong name for the first ALTER PROCEDURE, the edits I made to the second procedure are completed. Below is the short version of the transaction I've written:

```
BEGIN TRANSACTION
GO

ALTER PROCEDURE ProcessReport2 -- this procedure does not exist, causing an err

@@ERROR is reset after every statement. The error from your attempt to alter the first procedure is no longer detectable via @@ERROR after you successfully altered the second procedure. Here is an even simpler repro:

SELECT 1/0;
GO
SELECT @@ERROR; -- 8134

However if I put a successful statement in between:

SELECT 1/0;
GO
SELECT 1/1;
GO
SELECT @@ERROR; -- 0

In your case, you are doing this:

ALTER dbo.p1 ... -- fails
GO
-- @@ERROR here would be <> 0, but you're not checking it here!

ALTER dbo.p2 ... -- succeeds
GO
-- You're checking @@ERROR here, but success is 0

The only way your current logic would roll everything back is if the last ALTER failed. You need to check for @@ERROR after every ALTER. Here is an example.

USE tempdb;
GO
CREATE TABLE #x(err INT);
GO

BEGIN TRANSACTION;
GO

CREATE PROCEDURE dbo.foo -- fails
AS 
  SELECT foo FROM sys.objects;
GO
IF @@ERROR <> 0 
  INSERT #x(err) SELECT 1;
GO

CREATE PROCEDURE dbo.blat -- succeeds, but will get rolled back
AS 
  SELECT 1;
GO
IF @@ERROR <> 0 
  INSERT #x(err) SELECT 1;
GO

IF EXISTS (SELECT 1 FROM #x)
  ROLLBACK TRANSACTION;
ELSE
  COMMIT TRANSACTION;

DROP TABLE #x;

StackExchange Database Administrators Q#98697, answer score: 6