CODEWITHSUNDEEP
sqlsql-servert-sql
Jeff Stock
Jeff Stock

Reputation: 3856

Stop execution when ROLLBACK an SQL transaction

Is there a way to stop executing code once I ROLLBACK a transaction in T-SQL? For example, in the code below I want 'Message 1' to print, but not 'Message 2'.

BEGIN TRANSACTION
GO
PRINT 'Message 1'
ROLLBACK TRANSACTION
GO
PRINT 'Message 2'
GO

Upvotes: 0

Views: 963

Answers (3)

etoisarobot
etoisarobot

Reputation: 7814

In addition to Mike's statements about the GO keyword you could try something like this

BEGIN TRY
 Begin Transaction
  print 'Message1'
  Select 1/0 --throws error
 Commit Transaction
 print 'Message2'
END TRY
BEGIN CATCH
 rollback transaction
END CATCH

Upvotes: 3

Mike M.
Mike M.

Reputation: 12541

The GO statement is separating the batches, this means that even if the first one errors, the next batch will run. I'm assuming (and you know what that means...) that you're trying to circumvent if you've got an error.

You can look at GOTO, and have a error handling block. Or you can just have a RETURN; however, this needs to be within the same GO block

Example:

GO
  return
  SELECT 'Test 1'
GO
  SELECT 'Test 2'
GO

will still return Test 2, but not test 1.

Upvotes: 5

Vidar Nordnes
Vidar Nordnes

Reputation: 1364

If it's in a SP or similar, you can use RETURN

Upvotes: 0

Related Questions