Using @@Error in RaiseError

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
LVL 5
volkingAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
>> 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.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
volkingAuthor Commented:
@aneeshattingal
nope ... I want to raise whatever error was ACTUALLY captured in @FailErr
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Aneesh RetnakaranDatabase AdministratorCommented:
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

0
 
volkingAuthor 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.
0
 
Scott PletcherSenior DBACommented:
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.
0
 
volkingAuthor 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?
0
All Courses

From novice to tech pro — start learning today.