Recovery of the error message in a stored procedure

1

Hello

I have a stored procedure that does some processing

I wonder if on error I can retrieve the message in a variable

example:

date1 datetime declare @

select @ date1 = '20211' (by mistake)

the query analyzer I have this message I want to recover:

Msg 241, Level 16, State 1, Procedure MyProcedure, Line 272
Failed to convert a datetime value from a character string.

2

You can use TRY / CATCH, but there are some runtime errors that are critical and will abort the statement regardless. I suspect this one can be "caught" but off hand I'm not 100% sure

3

OK, I tried it :innocent:

BEGIN TRY

	DECLARE	@date1 datetime 
	SELECT	@date1 = '20211' -- (by mistake)
	SELECT	@date1 AS [@date1]
		, 'No error' AS [Message]
END TRY

BEGIN CATCH
	SELECT
		  ERROR_NUMBER() AS ErrorNumber
		, ERROR_SEVERITY() AS ErrorSeverity
		, ERROR_STATE() AS ErrorState
		, ERROR_PROCEDURE() AS ErrorProcedure
		, ERROR_LINE() AS ErrorLine
		, ERROR_MESSAGE() AS ErrorMessage;
END CATCH

does indeed trap the error

1 Like