We are currently creating an Application using SQL Server 7 and I need to figure out a way for Error Handling for Back End process. There will be multiple jobs that will be run which are called by Visual Basic.
I would need to create one procedure that should be called by all different Stored Procedures if an error Happens. I need to record that error to a table. What would be the best way of Error Handling. What options do I have?

What you are suggesting is not possible as it is possible to get errors (in fact quite likely with v7+) that are not trappable but return the error directly to the client.

You are probably better off defining an error class in VB and calling that on all errors.

I have implemented an errorhandling scheme with SQL 7.0 where the errormessages are stored in the master..sysmessages table and referred to by errorid. These errors can be passed between procedures with use of the RAISERROR() function and the master..sysmessages.error field is equal to the @@errorid of the RAISERROR() statement. The main limitation is that you can not use system errors, you have to code for all error cases and in the event that a system error occurs you have to substitute the user defined error in its place. The reason for this is that you can not reraise most system errors and the ones you can may or may not include variable substitution, which makes reraising the error impossible.

Sample:
IF (@variable IS NULL)
BEGIN
SET @ErrorID = xxxxx
GOTO ErrorHandler
END
---------------------
INSERT INTO TABLE()
VALUES()
SELECT @ErrorID = @@error
IF @ErrorID <> 0
BEGIN
IF @ErrorID > 50001
BEGIN
GOTO ErrorHandler
END
ELSE
BEGIN
SET @ErrorID = xxxxx
GOTO ErrorHandler
END
END
---------------------
Return 0
ErrorHandler:
IF @ErrorID <> 0
BEGIN
IF @ErrorID > 50001
BEGIN
RAISERROR(@ErrorID,16,1)
RETURN
END
ELSE
BEGIN
SET @ErrorID = xxx
RAISERROR(@ErrorID,16,1)
RETURN
END
END

Hope this helps,
Ryan