Please see if below given code helps in understanding what changes you might have to do to make it work

CREATE TABLE #LOG

(

ERROR VARCHAR(8000)

)

CREATE TABLE #TEST

(

ID SMALLINT

)

BEGIN TRY

BEGIN TRAN

INSERT INTO #TEST

SELECT 1000000

COMMIT

END TRY

BEGIN CATCH

ROLLBACK

INSERT INTO #LOG (ERROR)

SELECT ERROR_MESSAGE()

END CATCH

SELECT * FROM #TEST

SELECT * FROM #LOG

DROP TABLE #LOG

DROP TABLE #TEST