Does SQL Server have anything like VB's On Error GoTo?

To those familiar with Visual Basic, VB provides the option to use the ON ERROR GOTO ... clause to catch practically any error and handle it in one's program.Does SQL Server 2000 have the analogous clause that will jump to a certain part of code in a stored procedure to handle errors?

Solution: Errors must be checked after every SQL statement of interest.

In short: No! SQL Server does not have anything like Visual Basic's "On Error GoTo" construct. It acts as if there was always an "On Error Resume Next" statement in effect.
For effective error handling in a SQL Server stored procedure you must test the value of @@ERROR after EVERY SQL statement that might produce an error. Yes, that's a lot of testing. A well written stored procedure will often be 1/2 or more error handling code.
There's another important consideration in designing error handling: @@ERROR is reset after each and every SQL statement, @@ROWCOUNT is also similarly reset. Therefore, you must capture both of these immediatly after each SQL statement that might produce an error.
I start by declaring two variables to hold the values of @@ERROR and @@ROWCOUNT while the error handling code is working with them. They're declared at the top of every stored procedure and I've added them to my stored procedure template.

DECLARE @myERROR int -- Local @@ERROR , @myRowCount int -- Local @@ROWCOUNT
Next let's say that we had a simple INSERT statement
INSERT INTO Authors (au_id, au_fname, au_lname, contract) VALUES ('123-45-678' , 'Andrew' , 'Novick' , 1 )
Immediately after that statement, you should have the following statement:
SELECT @myERROR = @@ERROR , @myRowCOUNT = @@ROWCOUNT
These two assignements must be in the same statement. If you were to split them into two statements, for instance:
SET @myERROR = @@ERRORSET @myRowCOUNT = @@ROWCOUNT
then @myRowCOUNT would always be zero. That's because it's reflecting the number of rows effected by the SET @myERROR = @@ERROR statement that preceeds it.

Thanks,

Arun.