notes/sql_server/error_handling.sql

CREATE PROC RLARP.TEST AS

BEGIN
    PRINT 'Hi';                             --non-erroring statement
    create table #temp(x varchar(255));     --create a permanent object to call outside block after error
    insert into #temp select 1/0;
    insert into #temp select 'hi';          --fill it after error
    --select * from #temp;                    --select it after error
    PRINT ERROR_MESSAGE();                  --error message is gone

END;

begin transaction x
declare @e int;
DECLARE @em varchar(max);
begin try
    EXEC RLARP.TEST;
end TRY
begin CATCH
    select @e = ERROR_NUMBER(), @em = ERROR_MESSAGE();
    if @e <> 0
    BEGIN
        rollback transaction x;
        print @em;
    END
    if @e = 0 
    BEGIN
        commit transaction x;
        print 'ok';
    end
end catch

SELECT * FROM #temp