CREATE PROC [dbo].[USP_Test]
    @In_Value VARCHAR(30),
    @Status  INT OUTPUT
AS
BEGIN

 SET NOCOUNT ON;

 SET @Status = 0;

 PRINT '* USP_Test - Starting';
 PRINT '*     Input Parameter = ' + @In_Value;
 DECLARE @TestTable TABLE
     (
      TestCol1 INT NOT NULL
     );

 BEGIN TRY
  PRINT'*     Start of TRY block'
  INSERT INTO @TestTable(TestCol1)
   VALUES(NULL)
  PRINT '*     End of TRY block'
 END TRY
 BEGIn CATCH
  PRINT '*     Start of CATCH block'
  SET @Status = 1
  RAISERROR('Forced Error',15,1)
  PRINT '*     End of CATCH block'
 END CATCH

 PRINT '* USP_Test - Ending';

END

DECLARE @Status INT;

EXEC USP_Test 'Test Job String Value',
  @Status OUTPUT;

PRINT 'Return Value of @Status ' + CAST(@Status AS VARCHAR(6));

PRINT '* Step 2 of the job is running'

DECLARE @Status INT;

EXEC USP_Test 'Test Value',@Status OUTPUT;

PRINT 'Return Value of @Status = ' + CAST(@Status AS VARCHAR(6));

* USP_Test - Starting
*     Input Parameter = Test Value
*     Start of TRY block
*     Start of CATCH block
Msg 50000, Level 15, State 1, Procedure USP_Test, Line 28
Forced Error
*     End of CATCH block
* USP_Test - Ending
Return Value of @Status = 1

Job 'Test Job' : Step 1, 'Task 1' : Began Executing 2012-09-05
10:17:56

Msg 50000, Sev 15, State 1, Line 28 : Forced Error [SQLSTATE 42000]
Msg 0, Sev 15, State 1, Line 12 : * USP_Test - Starting [SQLSTATE 01000]
Msg 0, Sev 15, State 1, Line 13 : *     Input Parameter = Test Job String
Value [SQLSTATE 01000]
Msg 0, Sev 15, State 1, Line 20 : *     Start of TRY block [SQLSTATE 01000]
Msg 0, Sev 15, State 1, Line 26 : *     Start of CATCH block [SQLSTATE
01000]