Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: t-sql error handling

    ppl,

    i wrote this sql, seems v.straightforward to me.
    It works first time round, sets the foreigh key, but second time round should error out to the handler - but it just reports the errors to the message out and dosn't seem to fire the errhandler.

    Its gotta be a simple mistake - perhaps you could show me.


    DECLARE @ErrorMsg int

    BEGIN TRANSACTION

    ALTER TABLE TPH_GlobalProductHierarchy
    ADD CONSTRAINT SalesSubGrp_fk FOREIGN KEY (SalesSubGroupingID)
    REFERENCES TPH_SalesSubGrouping (ID)

    SET @ErrorMsg =@@ERROR
    IF @ErrorMsg <>0 GOTO ErrorHandler

    PRINT 'Success'
    COMMIT TRANSACTION

    ErrorHandler:
    IF @ErrorMsg <> 0
    BEGIN
    PRINT 'Rollback'
    ROLLBACK TRANSACTION
    END

  2. #2
    Join Date
    Jan 2004
    Posts
    49
    In this case check is not necessary....


    create procedure #add
    as

    ALTER TABLE TPH_GlobalProductHierarchy
    ADD CONSTRAINT SalesSubGrp_fk FOREIGN KEY (SalesSubGroupingID)
    REFERENCES TPH_SalesSubGrouping (ID)

    go

    DECLARE @ErrorMsg int

    BEGIN TRANSACTION
    exec #add
    SET @ErrorMsg =@@ERROR
    IF @ErrorMsg <>0 GOTO ErrorHandler

    PRINT 'Success'
    COMMIT TRANSACTION

    ErrorHandler:
    IF @ErrorMsg <> 0
    BEGIN
    PRINT 'Rollback'
    ROLLBACK TRANSACTION
    END
    go

    drop procedure #add
    go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •