volking
asked on
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
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
ASKER
@aneeshattingal
nope ... I want to raise whatever error was ACTUALLY captured in @FailErr
nope ... I want to raise whatever error was ACTUALLY captured in @FailErr
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
ASKER
@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.
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.
You can include the original error number in your error msg, but afaik you cannot trigger that specific error to re-occur.
ASKER
@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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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