How to optimize stored procedure so it won't end with "Out of memory exception"
You can do it in a single statement.

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRANSACTION;

DELETE FROM 
    Errn 
WHERE 
    erid IN (
             select 
                 distinct m.ID
             from 
                 Member m
             left join 
                 (select 
                      *, row_number() over (PARTITION BY rid order by ceid desc) as rn
                  from TypeA) x 
                 on m.ID = x.ID and x.rn = 1
             where 
                 (
                     (x.ceid is null and m.LastChangedDateTime &lt; @Deletion) 
                     or 
                     x.Resignation &lt; @Deletion
                 );

IF @@TRANCOUNT &gt; 0
    COMMIT TRANSACTION;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT &gt; 0
        ROLLBACK TRANSACTION;

    RAISERROR('ERR MSG', 16 1);

END CATCH