Post reply

how do we declarea label and rollback transaction

  • 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

  • March 22, 2010 at 6:16 pm

    #1137940

    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

  • March 23, 2010 at 2:17 am

    #1138019

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

    No replies to this thread please. Direct replies to: http://qa.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

  • March 23, 2010 at 2:29 am

    #1138026

    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.

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

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