Even if you don't explicitly state a return value, all stored procedures have one. I prefer to do something along these lines in 2005 (similar, but different error handling, in 2000).
CREATE MyProc
AS
BEGIN TRY
... write the query
END TRY
BEGIN CATCH
...more error handling as necessary
RETURN ERROR_NUMBER();
END CATCH
RETURN 0;
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning