We help IT Professionals succeed at work.

Using @@Error in RaiseError

volking
volking asked
on
How do I use RAISEERROR such that it simply re-raises an error captured earlier?

BEGIN TRAN MyTrans
Declare @FailErr int, @NewID int

INSERT MyTableA(varA, VarB) VALUES (@VarA, @VarB)
     SET @FailErr=@@ERROR, @NewID=@@IDENTITY
     IF @FailErr <> 0 GOTO ErrrorOut
          ... more tsql ...
INSERT MyTableB(varA, VarB) VALUES (@VarA, @VarB)
     SET @FailErr=@@ERROR, @NewID=@@IDENTITY
     IF @FailErr <> 0 GOTO ErrrorOut
          ... more tsql ...
INSERT MyTableC(varA, VarB) VALUES (@VarA, @VarB)
     SET @FailErr=@@ERROR, @NewID=@@IDENTITY
     IF @FailErr <> 0 GOTO ErrrorOut
          ... more tsql ...

COMMIT TRAN MyTrans
goto ExitSP

ErrrorOut:
ROLLBACK TRAN MyTrans

ExitSP:
if @FailErr <> 0
    RAISEERROR (??????? I want to raise @FailErr ????????)
ELSE
    Select @FailErr
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
BEGIN TRAN MyTrans
Declare @FailErr int, @NewID int

INSERT MyTableA(varA, VarB) VALUES (@VarA, @VarB)
     SET @FailErr=@@ERROR, @NewID=SCOPE_IDENTITY()
     IF @FailErr <> 0 GOTO ErrrorOut


INSERT MyTableB(varA, VarB) VALUES (@VarA, @VarB)
     SET @FailErr=@@ERROR, @NewID=SCOPE_IDENTITY()
     IF @FailErr <> 0 GOTO ErrrorOut


INSERT MyTableC(varA, VarB) VALUES (@VarA, @VarB)
     SET @FailErr=@@ERROR, @NewID=SCOPE_IDENTITY()
     IF @FailErr <> 0 GOTO ErrrorOut


COMMIT TRAN MyTrans
goto ExitSP

ErrrorOut:
ROLLBACK TRAN MyTrans

ExitSP:
if @FailErr <> 0
  RAISERROR ('Some Error Message', 16, 1)
 ELSE
    Select @FailErr

Author

Commented:
@aneeshattingal
nope ... I want to raise whatever error was ACTUALLY captured in @FailErr
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
I am not sure whether tht will work since RAISERROR accepts msgId's greater than 13000  and in case @@ERROR  is less than 13000, it will thrw some other error

Author

Commented:
@aneeshattingal - Your comment,  is exactly why I asked the question. I want to elegantly capture any errors but then Re-SPAWN the error later, after I've cleaned up. That way, my C# ADO call will receive exactly the error that actually happened.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
That's very hard to do in SQL Server 2000.

You can include the original error number in your error msg, but afaik you cannot trigger that specific error to re-occur.

Author

Commented:
@ScottPletcher

Ok forget elegant talk FUNCTIONAL. If my tsql starts a transaction then blindly does tsql and something fails (like foreign key violation, null into a non null field, etc) ... will the entire trans be automatically rolled back? Without my help? So, this is actually all I need?

BEGIN TRAN MyTrans
    INSERT MyTableA(varA, VarB) VALUES (@VarA, @VarB)
    INSERT MyTableB(varA, VarB) VALUES (@VarA, @VarB)
    INSERT MyTableC(varA, VarB) VALUES (@VarA, @VarB)
COMMIT TRAN MyTrans

And the rollback happens?
And my C# ADO will get the error?
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
>> If my tsql starts a transaction then blindly does tsql and something fails (like foreign key violation, null into a non null field, etc) ... will the entire trans be automatically rolled back? Without my help? <<

No.  Unfortunately it depends on the specific error.  Certain errors cause an automatic failure [at least assuming no TRY ... CATCH], other errors do not.

The only safe way is to test for errors and rollback yourself, as you're doing now.