Error handling problem in SQL Server

This can be a good way to handle errors in a stored procedure, however more often than not you would do that to log the error to a table:

begin catch
    insert into ErrorLog
    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

However, based on the fact that exceptions should only happen in exceptional circumstances, you almost certainly want to rethrow the error so that your code can handle this exception in the most rational way.

In sql server this is achieved using the RAISERROR (docs) command.

Your code would end up looking someting like:

BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- You could log the error here
    INSERT INTO ErrorLog(message, severity, state)
    VALUES(@ErrorMessage, @ErrorSeverity, @ErrorState)

    -- Use RAISERROR inside the CATCH block to return error
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;