My APP does a bad job of reacting to errors it gets from SQL - the error could be "anything" so has to be capable of interpreting the error and deciding if it is terminal or not. So if the APP gets a SQL Error it treats it as fatal.
I also need to check @@ROWCOUNT at times (e.g. "only 1 row expected to be processed"). So on that basis also checking @@ERROR might be "reasonable" compared to TRY/CATCH
My code is littered with checks for @@ERROR <> 0, if they ever fire the logic is to terminate the Sproc and return an error code, which the APP will treat as fatal (as distinct from a Soft Error e.g. where Sproc has a logic statement for a data-validation test which then fails)
UPDATE MyTable
SET SomeColumn =1
WHERE SomeID = @TheID
SELECT @MyErrNo = @@ERROR, @MyRowCount = @@ROWCOUNT
IF @MyErrNo <> 0 OR @MyRowCount <> 1
BEGIN
SELECT @OutputErrNo = 1234 -- Unique "location ID" within the Sproc
, @OutputMessage =
CONCAT('Error #1234:Update of MyTable'
, ', Error=', @MyErrNo
, ', Rows=', @MyRowCount
)
GOTO SprocExit
END
I never test that those @@ERROR statements work (i.e. by deliberately testing with @parameters that force each error), in fact I'm not sure what errors could arise that are not SQL-fatal, and would return a non-zero @@ERROR, in the first place. But I would write less code, and have more chance of a generic "catch all" using TRY/CATCH rather than @@ERROR test on every statement - for sure not EVERY statement has an @@ERROR check
My @@ERROR test to "just return an ErrorNo" is just boilerplate copy & paste, so probably will be bug-free ... it also logs / returns an ErrorNo unique for every such test so if some error-checking code "fires" then I have an ErrNo which uniquely identifies WHERE in the code the error had been raised. I don't suppose I would have that luxury with TRY/CATCH. (Yeah, "Line Number", but that presupposes that I have ready access to the source of the deployed version - will I even know, for a specific SProc, what that is? SProcs usually deployed with ENCRYPTION to stop well-meaning! people from meddling with them ... so sp_HelpText
would be sp_NoHelp
!)
Currently APP treats any SQL Error as fatal; in practice I achieve that by having four OUTPUT return @parameters: @ErrMsg
and @ErrNo
which is fatal if <> 0; also non-fatal: @ValidationCount
which is a count of the number of recoverable errors - not sure this has any use other than Zero / Non-Zero test, and a @ValidationMessage
which is one/many concatenated messages intended to be useful to the operator to fix the problem (and try again).
I did a Google and found a simple example which used the following code
CREATE TABLE #StudentDetails
(
MyRoll int NOT NULL
, MyName varchar(100) NOT NULL
)
Test #1a:
DECLARE @intErrNo int, @intRowCount int
PRINT 'Test #1a'
INSERT INTO #StudentDetails (MyRoll, MyName)
VALUES ('K', 'Kristen') -- Error : MyRoll is NOT numeric
-- SQL block terminated here
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
SELECT [TestName]='Test #1a', [@intErrNo]=@intErrNo, [@intRowCount]=@intRowCount
GO
For me the SQL terminates with the INSERT statement. If I then manually type SELECT @@ERROR
I do indeed get the Error No 245
.
I don't know of any setting?? that will allow this to fail (in an SProc) without a fatal error, so in practice, this error would not be trapped.
Whereas the Try/Catch Test #2:
PRINT 'Test #2a'
BEGIN TRY
INSERT INTO #StudentDetails (MyRoll, MyName)
VALUES ('K', 'Kristen') -- Error : MyRoll is NOT numeric
-- SQL THROWS a CATCH here
END TRY
BEGIN CATCH
SELECT [TestName]='Test #2a', [ErrorNumber]=ERROR_NUMBER(), [ErrorSeverity]=ERROR_SEVERITY(), [ErrorState]=ERROR_STATE(), [ErrorProcedure]=ERROR_PROCEDURE(), [ErrorLine]=ERROR_LINE(), [ErrorMessage]=ERROR_MESSAGE()
END CATCH
GO
definitely catches that error.
So if I change from @@ERROR to TRY/CATCH a whole load of previously fatal errors will become "caught" ... and then I'm right back to @JeffModen point of allowing the original SQL error to be returned to the APP as the better solution - whilst my APP will abort at that point, the APP will at least have logged the original SQL Error message, and that's all it used to do.
I also tried a non-fatal @@ERROR
test (trying to store NULL in a NOT NULL column)
SELECT [TestName]='Test #1a', [@intErrNo]=@intErrNo, [@intRowCount]=@intRowCount
GO
DECLARE @intErrNo int, @intRowCount int
PRINT 'Test #1b'
INSERT INTO #StudentDetails (MyRoll, MyName)
VALUES (NULL, 'Kristen') -- Error : MyRoll cannot be NULL
-- SQL error, but block not terminated
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
SELECT [TestName]='Test #1b', [@intErrNo]=@intErrNo, [@intRowCount]=@intRowCount
GO
this also issues a SQL Level=16 error, but continues. But my APP is going to abort as soon as it sees that SQL error, it won't be interested in the subsequent result set(s).
The ErrorState
is different in each case, maybe that's the salient info?
Same Test2b as TRY/CATCH:
PRINT 'Test #2b'
BEGIN TRY
INSERT INTO #StudentDetails (MyRoll, MyName)
VALUES (NULL, 'Kristen') -- Error : MyRoll cannot be NULL
-- SQL THROWS a CATCH here
END TRY
BEGIN CATCH
SELECT [TestName]='Test #2b', [ErrorNumber]=ERROR_NUMBER(), [ErrorSeverity]=ERROR_SEVERITY(), [ErrorState]=ERROR_STATE(), [ErrorProcedure]=ERROR_PROCEDURE(), [ErrorLine]=ERROR_LINE(), [ErrorMessage]=ERROR_MESSAGE()
END CATCH
GO