RAISERROR returns wrong line number of error in TRY/CATCH

[BFarley]

I have a stored procedure that uses the TRY/CATCH error handling. When an error occurs, the line number (ERROR_LINE) value is incorrect.

Msg 50000, Level 16, State 1, Procedure MyProcedure, Line 89
Divide by zero error encountered.
[/code'

First, this is the CATCH block:

[code]
BEGIN CATCH
	SET @strErrMsg = ERROR_MESSAGE()
	RAISERROR (@strErrMsg, 16, 1) 

	IF (ISNULL(@intErrNo,0) = 0 )
		SET @intErrNo = ERROR_NUMBER()
END CATCH

When an error goes to the CATCH block, it's line number (ERROR_LINE) shows as the line number of RAISERROR (@strErrMsg, 16, 1) , not the point at which the error number actually occurs. That's not really helpful for purposes of debugging.

Here is a full sample procedure:

CREATE PROC [dbo].[MyProcedure]
	(
	@strDepartmentName nvarchar(255),
	@strDescription nvarchar(255) = NULL,
	@fActive bit = 1,
	@intSortOrder tinyint = 1,
	@intDepartmentId int = NULL OUTPUT
	)
	
AS

BEGIN

	SET NOCOUNT ON
	
	DECLARE	@intErrNo int, 
			@strErrMsg varchar(4000)
			
	SELECT	@intErrNo = NULL,  
			@strErrMsg = NULL
		
	/* Check for required field DepartmentName */
	IF @strDepartmentName IS NULL
	
		 BEGIN 
			SET @intErrNo = 50001 
			SET @strErrMsg = 'Department name is required!'
			RAISERROR (@strErrMsg, 16, 1)
			RETURN
		 END
	
	/* Check whether DepartmentName already exists */
	IF EXISTS	(
				SELECT	Department 
				FROM	dbo.tblDepartments
				WHERE	Department = @strDepartmentName
				)
		BEGIN 
			SET @intErrNo = 50001
			SET @strErrMsg = 'Department name already exists!'
			RAISERROR (@strErrMsg, 16, 1)
		END	
			
	BEGIN TRY
	
		/* Purposely throw error */
		SELECT 1/0
	
		INSERT INTO dbo.tblDepartments
			(
			Department,
			Description,
			Active,
			SortOrder
			)
		VALUES
			(
			@strDepartmentName,
			@strDescription,
			@fActive,
			@intSortOrder
			)
			
		IF @@ROWCOUNT = 1
			SELECT	@intDepartmentId = SCOPE_IDENTITY()
		ELSE
			BEGIN
				SET @intErrNo = 50001 
				SET @strErrMsg = 'Could not add department!'
				RAISERROR (@strErrMsg, 16, 1)
				RETURN
			END				
	END TRY
	
	BEGIN CATCH
		SET @strErrMsg = ERROR_MESSAGE()
		RAISERROR (@strErrMsg, 16, 1) 

		IF (ISNULL(@intErrNo,0) = 0 )
			SET @intErrNo = ERROR_NUMBER()
	END CATCH
	
	RETURN @intErrNo
END

For the two validation errors (DepartmentName being null, or already exists) that appear prior to the TRY block, I do get the correct line number.

Msg 50000, Level 16, State 1, Procedure MyProcedure, Line 45
Application name already exists!

If these were to fall within the TRY block instead of before it, however, I get the line number of the RAISERROR statement in the CATCH block.

Msg 50000, Level 16, State 1, Procedure MyProcedure, Line 89
Application name already exists!

For the intention division by zero error, I get the line number of the RAISERROR statement in the CATCH block.

Msg 50000, Level 16, State 1, Procedure MyProcedure, Line 89
Divide by zero error encountered.

I realize I could create a custom error message value that contains ERROR_MESSAGE, ERROR_LINE, ERROR_PROCEDURE etc, but I'm trying to use whatever is natively there (and use it correctly!) first.

Thanks for any advice.

Bryant Farley

"The Dude Abides

 

[markros]

Which SQL Server version you're using? I haven't verified your code but to me it sounds like a possible bug.

Though in my SPs I simply used full error information concatenated using all error() functions provided.

 

[BFarley]

It's SQL Server 2008 Express.

Bryant Farley

"The Dude Abides

 

[markros]

Ok. For now try concatenated error message and see if the result is always correct.

 

[BFarley]

It does return the correct line number if the ERROR_LINE value is captured (with the CATCH block) and is part of a concatenated error message.

Sample:

Msg 50000, Level 16, State 1, Procedure MyProcedure, Line 89
Divide by zero error encountered.:8134,55,MyProcedure
[/sample]

For a user friendly error message, I can capture everything to the left of the colon, and then parse the other details out for logging purposes.

But the SqlException in ASP.NET would still show the error occurring on line 89.

Bryant Farley

"The Dude Abides

 

[markros]

I was returning error message by Raiserror to ASP.NET application. Unfortunately at the moment I can not get USB port correctly connected from my new computer here to show you some related code.

 

[markros]

I don't know if this is of any help, but take a look at the code from this thread

http://forums.asp.net/t/1407713.aspx

The error is generated using Raiserror and then you can get it using FormView.Inserted method and checking the Exception.