Best way to track "Errors" in Stored Procedure

Transact-SQL
1

Hi Guys,

I need advice. I have almost 30 Update statements with different logic in one stored procedure. If I didn't use any Error catch
It will very hard for me to find out which Update statement has errored out. Could anyone Please advice, Use Begin Catch/End Catch and
raise/Print error in all 30 update statements to find out what is the error and where is the error. Is this the best way to handle this?

Thank You.

2

You shouldn't have to wrap each update statement in TRY-CATCH. Use one try catch and print/save/use the error information that the try-catch feature provides. See the simplified example where I am printing out the line number on which the error happened.

There is documentation on available error information here.

I would also highly recommend the articles that Sommerskog has written on the topic. Very comprehensive and easy to follow.

CREATE  PROCEDURE dbo.TestProc
AS
	SET NOCOUNT, XACT_ABORT ON;

	BEGIN TRY
    
		CREATE TABLE #tmp(id1 INT NOT NULL, id2 INT NOT NULL );
		
		INSERT INTO #tmp VALUES (1,2);

		UPDATE #tmp SET id1 = NULL;

	END TRY
    BEGIN CATCH
		PRINT ERROR_MESSAGE();
		PRINT ERROR_LINE();
		IF (XACT_STATE() <> 0) ROLLBACK;

	END CATCH
 GO   

 EXEC dbo.TestProc;
1 Like
3

HI James,

Really appreciate your help.

Thank You.