Msg 207, Level 16, State 1, Procedure usp_Example_ErrorHandler, Line 46
Invalid column name 'notes'.
Msg 266, Level 16, State 2, Procedure usp_Example_ErrorHandler, Line 46
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.

CREATE PROCEDURE usp_Example_ErrorHandler

/* Example stored procedure to illustrate error handling 
* Example:
DECLARE @RC int
EXEC @RC = usp_Example_ErrorHandler
PRINT 'Return value = ' + CONVERT(varchar(10), @RC)
****************************************************************/ 
AS

DECLARE @myERROR int -- Local @@ERROR
       , @myRowCount int -- Local @@ROWCOUNT

SET NOCOUNT ON

BEGIN TRAN 
    INSERT INTO Authors (au_id, au_fname, au_lname, contract)
         VALUES ('222-22-2222'
                , 'Andrew'
                , 'Novick'
                , 1
                )

    SELECT @myERROR = @@ERROR
    IF @myERROR != 0 GOTO HANDLE_ERROR
 
    INSERT INTO  titles(title_id, title, type, price,notes, pubdate)
           VALUES('WW0790'
               , 'Transact-SQL User-Defined Functions'
               , 'popular_comp',  49.95
               , 'Great book.', '2003-11-04')
    SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
    IF @myERROR != 0 GOTO HANDLE_ERROR

    INSERT INTO titleauthor (au_id, title_id)
           VALUES('222-22-2222', 'WW0790')
    SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
    IF @myERROR != 0 GOTO HANDLE_ERROR

    COMMIT TRAN -- No Errors, so go ahead

    RETURN 0

HANDLE_ERROR:
    ROLLBACK TRAN
    RETURN @myERROR
GO

CREATE TABLE #myTable99(Col1 int, Col2 char(1))
GO

DECLARE @Error int, @Rowcount int

BEGIN TRY
	INSERT INTO #myTable99(Col1, Col2, notes)
	SELECT 1, 'a', 'These are my Notes'

	SELECT @Error = @@ERROR, @Rowcount = @@ROWCOUNT
END TRY

BEGIN CATCH
    SELECT	  [Error_Line]		= ERROR_LINE()
			, [Error_Number]	= ERROR_NUMBER()
			, [Error_Severity]	= ERROR_SEVERITY()
			, [Error_State]		= ERROR_STATE()
			, [Error_Message]	= ERROR_MESSAGE()
	SELECT @Error AS [Error], @Rowcount AS [Rowcount]
END CATCH
GO

DROP TABLE #myTable99
GO

quote:

---

Originally posted by X002548

Also, DON'T use Return to return your @RC.

---