Error handling questions

In my sprocs all SQL calls have TRY CATCH
In CATCH, I want to log all error functions ERROR_MESSAGE(), ERROR_NUMBER()
etc. etc. to a table.
Can I call another sproc to do this or is the scope of the error functions
only in the sproc where the error occurs?
(I guess I'm also wary that the called sproc may itself get an error which
overlays the original error)
Also, it seems I cant use the error functions directly in an INSERT..
VALUES.. list but first have to declare a variable and assign the function
return value. Is this right?
How is this best done with minimal code in my sprocs?

Thanks, you guys have been very patient and helpful so far

You can use a stored procedure to log the errors. Look at the examples
in SQL Server Books Online:

USE AdventureWorks;
GO

-- Verify that the stored procedure does not exist.
IF OBJECT_ID ('usp_GetErrorInfo', 'P') IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create a procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
GO

BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
-- Execute the error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;
GO


Plamen Ratchev
http://www.SQLStudio.com