Find if Stored Procedure Error
If an error condition in your stored procedure raises an exception, your only option without changing the code inside the procedure, really, is to wrap the call in try/catch:

BEGIN TRY;
  EXEC sys.sp_helpdb @foo = 1;
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE();
END CATCH;


If your procedure returns an error number without bubbling up an exception, e.g.:

CREATE PROCEDURE dbo.bob
  @fail bit = 0
AS
BEGIN
  IF (@fail = 1)
  BEGIN
    RETURN -1;
  END
  -- unnecessary, but:
  ELSE
  BEGIN
    RETURN 0;
  END
END


Then you can use this format:

DECLARE @TestErrorFlag int;
EXEC @TestErrorFlag= dbo.bob @fail = 1;
PRINT @TestErrorFlag;


Of course in this case if any error condition in the procedure does lead to an exception, the PRINT won't happen.



Error handling is a massive topic. I would browse around these articles:


Error and Transaction Handling in SQL Server - Part 1
Error and Transaction Handling in SQL Server - Part 2
Error and Transaction Handling in SQL Server - Part 3