While getting a handle on how to trap errors in stored procedures and how
this works when the procedure is called from within a job, I encountered
some odd behaviour which I would like to try and have someone help me
It's not that there's a problem as such, more a lack of understanding why
things work the way they do, so please bear with me as in order to try and
illustrate the mystery I need to show you what I'm doing.
OK, first I have a simple test stored procedure which forms the core of my
testing as follows:
CREATE PROC [dbo].[USP_Test]
@Status INT OUTPUT
SET NOCOUNT ON;
SET @Status = 0;
PRINT '* USP_Test - Starting';
PRINT '* Input Parameter = ' + @In_Value;
DECLARE @TestTable TABLE
TestCol1 INT NOT NULL
PRINT'* Start of TRY block'
INSERT INTO @TestTable(TestCol1)
PRINT '* End of TRY block'
PRINT '* Start of CATCH block'
SET @Status = 1
PRINT '* End of CATCH block'
PRINT '* USP_Test - Ending';
In order to test certain behaviours I am calling this SP in two ways.
The first is within a job I've created with two steps that I've called
"TestJob". Both of this job's steps are T-SQL and are coded as follows:
Step 1 -
DECLARE @Status INT;
EXEC USP_Test 'Test Job String Value',
PRINT 'Return Value of @Status ' + CAST(@Status AS VARCHAR(6));
Step 2 -
PRINT '* Step 2 of the job is running'
The second way I call my test SP is via SSMS's query window like this:
DECLARE @Status INT;
EXEC USP_Test 'Test Value',@Status OUTPUT;
PRINT 'Return Value of @Status = ' + CAST(@Status AS VARCHAR(6));
I've configured the job steps to send output into a txt file for viewing the
results of job and SP execution.
When I execute the T-SQL code via SSMS, the results window shows the
* 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
* End of CATCH block
* USP_Test - Ending
Return Value of @Status = 1
So far, so good. I see the generated error message from RAISERROR() shown
where I expected I.e. at the point of the caught error in the CATCH block.
However, when I look at the results of the output from my test job calling
the same SP, I see the following:
Job 'Test Job' : Step 1, 'Task 1' : Began Executing 2012-09-05
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
Two things puzzle me about the above results.
The first is the fact that the forced error produced via
RAISERROR() is reported BEFORE the SP's PRINT statement steps? I don't
understand the non-linear nature of this.
The second is why each PRINT statement result is
reported as an error with an error message of zero. I would have expected to
see the results of the PRINT statements output as normal before the
RAISERROR() statement was encountered and then the forced error message and
then nothing as the SP within the job would exit at that point with the
RAISERROR() level 15.
The other odd thing is that if I replace the first PRINT command within the
TRY block with a SELECT instead, then the output of this SELECT is shown
BEFORE the forced error text.
So, it seems that using SELECT shows results in a more linear fashion than PRINT does.
I would be grateful if anyone can please enlighten me as to why the above