how do we declarea label and rollback transaction

Question

Post reply

how do we declarea label and rollback transaction

SQLTestUser

Hall of Fame

Points: 3997
More actions
March 22, 2010 at 4:49 pm

#386558

I am trying to create a rollback and commit transaction as shown below,but when i run the script i get an error as
A GOTO statement references the label 'Error_Handler' but the label has not been declared. Is there a better way to rollback transction, i am trying to rollback trnasaction on a bunch of queries, do i have to rollback one at a time ??
Any help od insight would be awesome
The script used is
Declare @Error int
BEGIN TRANSACTION
CREATE TABLE [dbo].[XXX](
[a] [smallint] NOT NULL,
[nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[c] [tinyint] NOT NULL,
[d] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_XXX_1] PRIMARY KEY CLUSTERED
(
[PKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
SET @Error = @@ERROR
IF (@Error <> 0) GOTO Error_Handler
COMMIT TRAN
Error_Handler:
IF @Error <> 0
Begin
Rollback Transaction
PRINT 'Unexpected error occurred!'
END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

You might want to take a look at TRY/CATCH blocks, i.e.:

BEGIN TRANSACTION

BEGIN TRY

CREATE TABLE [dbo].[XXX](

[a] [smallint] NOT NULL,

[nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[c] [tinyint] NOT NULL,

[d] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_XXX_1] PRIMARY KEY CLUSTERED ([a] ASC)

WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END TRY

BEGIN CATCH

if @@TranCount > 0 ROLLBACK TRANSACTION

print 'Unexpected error'

END CATCH

if @@TranCount > 0 COMMIT TRANSACTION

The first time this is run, the [xxx] table will be created. The second time, you will get the 'Unexpected error' message.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Please don't cross post. It just wastes peoples time and fragments replies.

No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic887768-391-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vidya_pande SSCertifiable Points: 5275 More actions · Answer 3

This is not working if first time you get some error say because-

Column is not correct for constraint or data type is not correct.