/*
данный скрипт работает только на SQL2016+
предназначен для замены сертификатов на новую длину ключа, соответствующую версии сервера (sql2016 - 2048бит, sql2022 - 3072бит)
запускать на рабочей базе энергосферы. предварительно неплохо бы сделать её бакап.

ВАЖНО!!!! Если наш сертификат из dbo.CertInfo использовался для шифрования нестандартных пользовательских данных, то скрипт выполнять нельзя. Необходимы дополнительные действия по их перекодированию.

Если шифрование не использовалось (таблицы MeterPasswordChange, MeterPasswordChangeCommand, USDBinaries пусты), то будет выполнена простая перегенерация сертификатов.
Если же использовалось, то будет создана временная база данных esDBforChangeCert (со старым уровнем совместимости, чтобы была возможность использовать старый сертификат), 
в которой будут созданы копия существующего сертификата и частичные копии таблиц с зашифрованными данными.

Во время работы скрипта будет недоступна работа с шифрованием (ПУД, USDBinaries), т.к. старые сертификаты из рабочей базы будут удалены. 

По окончанию работы скрипта можно повысить уровень совместимости рабочей базы до актуального (если этого не было сделано раньше) и проверить работу шифрования.
После проверки при корректной работе шифрования с новым сертификатом временную базу данных esDBforChangeCert можно удалить (для гарантии можно предварительно сделать её бакап).
*/
declare @existsOldCertInTbl bit;
declare @existsOldCertInSysTbl bit;
declare @existsDB bit;
declare @sqlVersion int;
declare @certName sysname;
declare @keyLength int;
declare @newkeyLength int;
declare @compabilityLevel varchar(3);
declare @strSQL nvarchar(max);
declare @Cmd_dop varchar(max);
declare @Cmd varchar(max);
declare @LastStep int;
declare @certData varbinary(8000);
declare @certKey varbinary(8000);
declare @rowcount int;
declare @message varchar(max);

set @certName = 'ProsoftDBCert_2'; -- !!! скрипт сотстоит из двух батчей, при изменении поменять в двух местах
set @existsOldCertInTbl = 0;
set @existsOldCertInSysTbl = 0;
set @existsDB = 0;

set nocount on;
set xact_abort on;

begin try
    set @sqlVersion = dbo.fGetSQLVersion();
    if @sqlVersion < 13
    begin
        raiserror('Версия SQL-сервера должна быть не ниже 2016.', 16, 1);
    end;

    select @keyLength = c.key_length,
        @existsOldCertInSysTbl = 1
    from sys.certificates as c 
    where c.name = @certName;

    set @existsOldCertInSysTbl = coalesce(@existsOldCertInSysTbl, 0);

    if exists(select 1 from dbo.CertInfo as ci where ci.CertName = @certName)
    begin
        set @existsOldCertInTbl = 1;
    end;

    if exists(select 1 from sys.databases as db where db.name = 'esDBforChangeCert')
    begin
        set @existsDB = 1;
    end;

    if @existsDB = 0
        and ((@existsOldCertInSysTbl = 1 and @existsOldCertInTbl = 0)
            or (@existsOldCertInSysTbl = 0 and @existsOldCertInTbl = 1)
            )
    begin -- т.к. базы нет, значит мы еще не могли сертификаты удалять. но их не полный комплект, что странно, но не страшно - досоздадим недостающее
        exec dbo.CertCreate;

        if @existsOldCertInSysTbl = 0
        begin -- если сертификат отсутствовал в системой таблице, то получим длину ключа
            select @keyLength = c.key_length,
                @existsOldCertInSysTbl = 1
            from sys.certificates as c 
            where c.name = @certName;
        end;
    end;

    if @existsOldCertInTbl = 1 -- сертификат есть в нашей таблице
        and @existsOldCertInSysTbl = 1 -- сертификат есть в системной таблице
        and (@keyLength = case when @sqlVersion < 16 then 2048 else 3072 end) -- а также имеет правильную длину
        and (@existsDB = 0) -- и нет базы перекачки
    begin -- ничего делать не надо, выходим
        raiserror('Сертификат в базе имеет правильную длину. Смена сертификатов не требуется.', 16, 1);
    end;

    if not exists(select 1 from dbo.USDBinaries)
        and not exists(select 1 from MeterPasswordChange as mpc where mpc.Password is not null)
        and not exists(select 1 from MeterPasswordChangeCommand as mpcc where mpcc.NewPassword is not null or mpcc.OldPassword is not null)
    begin -- если нет закодированных данных, то можно просто удалить старый сертификат (если он вообще существует) и создать новый
        delete from dbo.CertInfo where CertName = @certName;

        if @existsOldCertInSysTbl = 1
        begin
            set @strSQL = 'drop certificate ' + quotename(@certName) + ';'
            exec sp_executesql @strSQL;
        end;

        exec dbo.CertCreate;
        raiserror('Сертификат успешно пересоздан.', 10, 1);
    end
    else -- закодированные данные есть, надо создавать базу перекачки, если ещё нет, копировать старый сертификат, создавать новый и перекодировать
    begin
        if @existsDB = 0 -- создадим базу для миграции
        begin
            create database esDBforChangeCert;
        end;

        if not exists(select 1 from esDBforChangeCert.sys.tables as tbl where tbl.name = 'LogChangeCert')
        begin
            set @strSQL = 'create table esDBforChangeCert.dbo.LogChangeCert(ID int not null, Info varchar(max), DT datetime default getdate());'
            exec sp_executesql @strSQL;
        end;
     
        select top (1) @LastStep = q1.ID from esDBforChangeCert.dbo.LogChangeCert as q1 order by q1.ID desc;

        set @LastStep = coalesce(@LastStep, 0);

        if @LastStep < 9 -- @LastStep = 9-10 - это шаги удаления исходных сертификатов
            and @existsOldCertInSysTbl = 0
            and @existsOldCertInTbl = 0
        begin -- в исходной базе нет сертификата ни в нашей, ни в системной таблице и мы его еще не удаляли после копирования в базу миграции ранее
            raiserror('Не обнаружены сертификаты, перекодирование зашифрованных данных невозможно. Необходимо восстановить сертификат из бакапа БД.', 16, 1);
        end;

        if @LastStep >= 9 -- @LastStep = 9 - это шаг удаления сертификата из CertInfo
            and @LastStep < 23 -- @LastStep = 23 - это шаг копирования сертификата в рабочую базу в CertInfo из базы esDBforChangeCert
            and @existsOldCertInTbl = 1
        begin -- в исходной базе в CertInfo обнаружен новый сертификат, хотя мы его уже удалили. ругаемся, надо разбираться отдельно.
            raiserror('Обнаружен новый сертификат в таблице CertInfo, которым возможно были закодированы данные. Необходимо ручное вмешательство.', 16, 1);
        end;
        
        if @LastStep >= 10 -- @LastStep = 10 - это шаг удаления сертификата из sys.certificates
            and @LastStep < 24 -- @LastStep = 24 - это шаг создания сертификата на основе CertInfo
            and @existsOldCertInSysTbl = 1
        begin -- в исходной базе в sys.certificates обнаружен новый сертификат, хотя мы его уже удалили. ругаемся, надо разбираться отдельно.
            raiserror('Обнаружен новый сертификат в таблице sys.certificates, которым возможно были закодированы данные. Необходимо ручное вмешательство.', 16, 1);
        end;

        if @LastStep = 0
        begin
            --исходя из длины ключа подберём уровень совместимости базы, чтобы можно было этим сертификатом расшифровать
            set @compabilityLevel = case @keyLength 
                                        when 1024 then '120' -- сертификат из <=sql2014
                                        when 2048 then '130' -- sql2016 <=сертификат < sql2022
                                    end;

            set @strSQL = 'alter database esDBforChangeCert set compatibility_level = ' + @compabilityLevel + ';';
            exec sp_executesql @strSQL;
            
            set @LastStep = 1;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Необходимый уровень совместимости ' + @compabilityLevel + ' для базы esDBforChangeCert установлен.');
        end;
        
        if @LastStep = 1
        begin
            set @strSQL = 'create table esDBforChangeCert.dbo.CertInfo(CertName varchar(200) not null, PublicKey varbinary(max) not null, PrivateKey varbinary(max) not null);'
            exec sp_executesql @strSQL;

            set @LastStep = 2;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Создана таблица CertInfo.');
        end;

        if @LastStep = 2
        begin
            select @strSQL = q1.definition
            from sys.sql_modules as q1
            where q1.object_id = object_id('dbo.DataDecrypt', 'FN');
            exec esDBforChangeCert..sp_executesql @strSQL;
            
            set @LastStep = 3;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Из исходной базы скопирована функция декодирования.');
        end;

        if @LastStep = 3
        begin
            select @strSQL = q1.definition
            from sys.sql_modules as q1
            where q1.object_id = object_id('dbo.DataEncrypt', 'FN');

            set @strSQL = replace(@strSQL, @certName, @certName + '_new');
            set @strSQL = replace(@strSQL, '0x50726F736F66744442436572745F32', '0x50726F736F66744442436572745F325F6E6577');
            exec esDBforChangeCert..sp_executesql @strSQL;
            
            set @LastStep = 4;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Из исходной базы скопирована функция кодирования.');
        end;

        if @LastStep = 4
        begin
            insert into esDBforChangeCert.dbo.CertInfo(CertName, PublicKey, PrivateKey)
            select ci.CertName, ci.PublicKey, ci.PrivateKey
            from dbo.CertInfo as ci 
            where ci.CertName = @certName;

            if @@rowcount <> 1
            begin
                raiserror('Не удалось скопировать старый сертификат в базу esDBforChangeCert.', 16, 1);
            end;

            set @LastStep = 5;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Сертификат из базы-источника скопирован в CertInfo.');
        end;

        if @LastStep = 5
        begin
            select @certData = ci.PublicKey,
                @certKey = ci.PrivateKey
            from esDBforChangeCert.dbo.CertInfo as ci
            where ci.CertName = @certName;

            set @Cmd_dop = 0x7573652065734442666F724368616E6765436572743B206372656174652063657274696669636174652050726F736F66744442436572745F322066726F6D2062696E617279203D20
            set @Cmd = @Cmd_dop + convert(varchar(max), @certData, 1);
            set @Cmd_dop = 0x20776974682070726976617465206B65792862696E617279203D20
            set @Cmd = @Cmd + @Cmd_dop + convert(varchar(max), @certKey, 1);
            set @Cmd_dop = 0x2C2064656372797074696F6E2062792070617373776F7264203D202730255561233D664D666041636C4754516175272C20656E6372797074696F6E2062792070617373776F7264203D202751617A32457274372450584337355F683467272029
            set @Cmd = @Cmd + @Cmd_dop;
            exec (@Cmd);

            if not exists(select 1 from esDBforChangeCert.sys.certificates as q1  where q1.name = @certName)
            begin
                raiserror('Не удалось создать сертификат в системной таблице на основании старого в базе esDBforChangeCert.', 16, 1);
            end;

            set @LastStep = 6;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Создание старого сертификата в sys.certificates.');
        end;

        if @LastStep = 6
        begin
            set @Cmd = 0x7573652065734442666F724368616E6765436572743B206372656174652063657274696669636174652050726F736F66744442436572745F325F6E657720656E6372797074696F6E2062792070617373776F7264203D202751617A32457274372450584337355F683467272077697468207375626A656374203D202750726F736F6674204442204365727469666963617465272C2073746172745F64617465203D20273230303030313031272C206578706972795F64617465203D2027353030303031303127
            exec(@Cmd);

            set @LastStep = 7;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Создание нового сертификата в sys.certificates.');
        end;

        if @LastStep = 7
        begin
            set @Cmd = 0x7573652065734442666F724368616E6765436572743B2073656C65637420404365727444617461203D2063657274656E636F64656428637274662E63657274696669636174655F6964292C2040436572744B6579203D2063657274707269766174656B657928637274662E63657274696669636174655F69642C204E2730255561233D664D666041636C4754516175272C204E2751617A32457274372450584337355F68346727292066726F6D207379732E636572746966696361746573206173206372746620776865726520637274662E6E616D65203D202750726F736F66744442436572745F325F6E657727

            set @strSQL = @Cmd;
            exec sp_executesql @strSQL, N'@CertData varbinary(8000) output, @CertKey varbinary(8000) output', @CertData = @certData output, @CertKey = @certKey output;

            insert esDBforChangeCert.dbo.CertInfo (CertName, PublicKey, PrivateKey)
            values (@certName + '_new', @certData, @certKey);
            set @LastStep = 8;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Создание нового сертификата в CertInfo.');
        end;
  
        if @LastStep = 8
        begin --удаляем старый сертификат, чтобы никто не мог во время перекачки шифровать новые данные
            delete from dbo.CertInfo where CertName = @certName;

            set @LastStep = 9;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Удаление старого сертификата в рабочей базе из CertInfo.');
        end;

        if @LastStep = 9
        begin --удаляем старый сертификат, чтобы никто не мог во время перекачки шифровать новые данные
            set @strSQL = 'drop certificate ' + @certName + ';'
            exec sp_executesql @strSQL;

            if exists(select 1 from sys.certificates as q1  where q1.name = @certName)
            begin
                raiserror('Не удалось удалить старый сертификат из sys.certificates в рабочей базе.', 16, 1);
            end;

            set @LastStep = 10;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Удаление старого сертификата в рабочей базе из sys.certificates.');
        end;

        if @LastStep = 10
        begin
            set @strSQL = 'create table esDBforChangeCert.dbo.USDBinaries(ID_Rec int identity(1,1) unique, ID_USPD int not null, Num int not null, Encoded varbinary(max) not null, Encoded_new varbinary(max), IsRecoded bit not null default 0, IsProcessed bit not null default 0,';
            set @strSQL = @strSQL + ' constraint PK_USDBinaries primary key clustered(ID_USPD, Num));';
            exec sp_executesql @strSQL;
            
            set @LastStep = 11;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Создание таблицы для перекодирования данных в USDBinaries.');
        end;

        if @LastStep = 11
        begin
            set @strSQL = 'create table esDBforChangeCert.dbo.MeterPasswordChange(ID_Rec int identity(1,1) unique, ID_MeterPasswordChange int not null, Password varbinary(max) not null, Password_new varbinary(max), IsRecoded bit not null default 0, IsProcessed bit not null default 0,';
            set @strSQL = @strSQL + ' constraint PK_MeterPasswordChange primary key clustered(ID_MeterPasswordChange));';
            exec sp_executesql @strSQL;

            set @LastStep = 12;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Создание таблицы для перекодирования данных в MeterPasswordChange.');
        end;
        
        if @LastStep = 12
        begin
            set @strSQL = 'create table esDBforChangeCert.dbo.MeterPasswordChangeCommand(ID_Rec int identity(1,1) unique, ID_Cmd uniqueidentifier not null, OldPassword varbinary(max), NewPassword varbinary(max), OldPassword_new varbinary(max), NewPassword_new varbinary(max), IsRecoded bit not null default 0, IsProcessed bit not null default 0,';
            set @strSQL = @strSQL + ' constraint PK_MeterPasswordChangeCommand primary key clustered(ID_Cmd));';
            exec sp_executesql @strSQL;

            set @LastStep = 13;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Создание таблицы для перекодирования данных в MeterPasswordChangeCommand.');
        end;        
        
        if @LastStep = 13
        begin
            insert into esDBforChangeCert.dbo.USDBinaries(ID_USPD, Num, Encoded)
            select usdb.ID_USPD, usdb.Num, usdb.Encoded
            from dbo.USDBinaries as usdb;

            set @rowcount = @@rowcount;

            set @LastStep = 14;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Копирование данных для перекодирования в USDBinaries. Скопировано строк: ' + cast(@rowcount as varchar(10)));
        end;        
        
        if @LastStep = 14
        begin
            insert into esDBforChangeCert.dbo.MeterPasswordChange(ID_MeterPasswordChange, Password)
            select mpc.ID_MeterPasswordChange, mpc.Password
            from dbo.MeterPasswordChange as mpc
            where mpc.Password is not null;

            set @rowcount = @@rowcount;

            set @LastStep = 15;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Копирование данных для перекодирования в MeterPasswordChange. Скопировано строк: ' + cast(@rowcount as varchar(10)));
        end;       
        
        if @LastStep = 15
        begin
            insert into esDBforChangeCert.dbo.MeterPasswordChangeCommand(ID_Cmd, OldPassword, NewPassword)
            select mpcc.ID_Cmd, mpcc.OldPassword, mpcc.NewPassword
            from dbo.MeterPasswordChangeCommand as mpcc
            where mpcc.OldPassword is not null
                or mpcc.NewPassword is not null;

            set @rowcount = @@rowcount;

            set @LastStep = 16;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Копирование данных для перекодирования в MeterPasswordChangeCommand. Скопировано строк: ' + cast(@rowcount as varchar(10)));
        end; 
    end;
end try
begin catch
    if @@trancount <> 0
    begin
        rollback;
    end;

    throw;
end catch
go
declare @certName sysname;
declare @LastStep int;
declare @batchSize int;
declare @idRec int;
declare @idRecMax int;

set @certName = 'ProsoftDBCert_2'; -- !!! скрипт сотстоит из двух батчей, при изменении поменять в двух местах
set @batchSize = 5000;

-- само перекодирование пришлось вынести в отдельный батч, т.к. уровень смена уровня совместимости действует не сразу 
if exists(select 1 from sys.databases as db where db.name = 'esDBforChangeCert')
begin
    begin try
        select top (1) @LastStep = q1.ID from esDBforChangeCert.dbo.LogChangeCert as q1 order by q1.ID desc;
        set @LastStep = coalesce(@LastStep, 0);

        if @LastStep = 16
        begin
            while 1 = 1
            begin
                update top (@batchSize) tbl
                set Encoded_new = esDBforChangeCert.dbo.DataEncrypt(esDBforChangeCert.dbo.DataDecrypt(tbl.Encoded)),
                    IsRecoded = 1
                from esDBforChangeCert.dbo.USDBinaries as tbl
                where tbl.IsRecoded = 0;

                if @@rowcount = 0
                begin
                    break;
                end;
            end;

            if exists(select 1 
                        from esDBforChangeCert.dbo.USDBinaries as tbl
                        where tbl.Encoded_new is null
                        )
            begin
                raiserror('Не все данные в USDBinaries удалось корректно перекодировать новым сертификатом.', 16, 1);
            end;

            set @LastStep = 17;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Перекодирование данных в USDBinaries успешно завершено.');
        end;              
        
        if @LastStep = 17
        begin
            while 1 = 1
            begin
                update top (@batchSize) tbl
                set Password_new = esDBforChangeCert.dbo.DataEncrypt(esDBforChangeCert.dbo.DataDecrypt(tbl.Password)),
                    IsRecoded = 1
                from esDBforChangeCert.dbo.MeterPasswordChange as tbl
                where tbl.IsRecoded = 0;

                if @@rowcount = 0
                begin
                    break;
                end;
            end;

            if exists(select 1 
                        from esDBforChangeCert.dbo.MeterPasswordChange as tbl
                        where tbl.Password_new is null
                        )
            begin
                raiserror('Не все данные в MeterPasswordChange удалось корректно перекодировать новым сертификатом.', 16, 1);
            end;

            set @LastStep = 18;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Перекодирование данных в MeterPasswordChange успешно завершено.');
        end; 

        if @LastStep = 18
        begin
            while 1 = 1
            begin
                update top (@batchSize) tbl
                set OldPassword_new = case when tbl.OldPassword is null then null else esDBforChangeCert.dbo.DataEncrypt(esDBforChangeCert.dbo.DataDecrypt(tbl.OldPassword)) end,
                    NewPassword_new = case when tbl.NewPassword is null then null else esDBforChangeCert.dbo.DataEncrypt(esDBforChangeCert.dbo.DataDecrypt(tbl.NewPassword)) end,
                    IsRecoded = 1
                from esDBforChangeCert.dbo.MeterPasswordChangeCommand as tbl
                where tbl.IsRecoded = 0;

                if @@rowcount = 0
                begin
                    break;
                end;
            end;

            if exists(select 1 
                        from esDBforChangeCert.dbo.MeterPasswordChangeCommand as tbl
                        where (tbl.OldPassword_new is null and tbl.OldPassword is not null)
                            or (tbl.NewPassword_new is null and tbl.NewPassword is not null)
                        )
            begin
                raiserror('Не все данные в MeterPasswordChangeCommand удалось корректно перекодировать новым сертификатом.', 16, 1);
            end;

            set @LastStep = 19;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Перекодирование данных в MeterPasswordChangeCommand успешно завершено.');
        end;              
        
        if @LastStep = 19
        begin
            set @idRec = null;
            set @idRecMax = null;

            select @idRec = min(q1.ID_Rec), @idRecMax = max(q1.ID_Rec) from esDBforChangeCert.dbo.USDBinaries as q1 where q1.IsProcessed = 0;
            
            while @idRec <= @idRecMax
            begin
                begin tran;

                update sourc
                set Encoded = tbl.Encoded_new
                from dbo.USDBinaries as sourc
                    inner join esDBforChangeCert.dbo.USDBinaries as tbl on sourc.ID_USPD = tbl.ID_USPD and sourc.Num = tbl.Num
                where tbl.IsProcessed = 0
                    and tbl.ID_Rec >= @idRec
                    and tbl.ID_Rec < @idRec + @batchSize;

                update tbl
                set IsProcessed = 1
                from esDBforChangeCert.dbo.USDBinaries as tbl
                where tbl.IsProcessed = 0
                    and tbl.ID_Rec >= @idRec
                    and tbl.ID_Rec < @idRec + @batchSize;

                set @idRec = @idRec + @batchSize - 1;

                commit tran;
            end;

            if exists(select 1 from esDBforChangeCert.dbo.USDBinaries as tbl where tbl.IsProcessed = 0)
            begin
                raiserror('Не все перекодированнные данные в USDBinaries скопированы в рабочую БД.', 16, 1);
            end;

            set @LastStep = 20;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Перекодированные данные в USDBinaries успешно скопированы в рабочую базу.');
        end;              
        
        if @LastStep = 20
        begin
            set @idRec = null;
            set @idRecMax = null;

            select @idRec = min(q1.ID_Rec), @idRecMax = max(q1.ID_Rec) from esDBforChangeCert.dbo.MeterPasswordChange as q1 where q1.IsProcessed = 0;
            
            while @idRec <= @idRecMax
            begin
                begin tran;

                update sourc
                set Password = tbl.Password_new
                from dbo.MeterPasswordChange as sourc
                    inner join esDBforChangeCert.dbo.MeterPasswordChange as tbl on sourc.ID_MeterPasswordChange = tbl.ID_MeterPasswordChange
                where tbl.IsProcessed = 0
                    and tbl.ID_Rec >= @idRec
                    and tbl.ID_Rec < @idRec + @batchSize;

                update tbl
                set IsProcessed = 1
                from esDBforChangeCert.dbo.MeterPasswordChange as tbl
                where tbl.IsProcessed = 0
                    and tbl.ID_Rec >= @idRec
                    and tbl.ID_Rec < @idRec + @batchSize;

                set @idRec = @idRec + @batchSize - 1;

                commit tran;
            end;

            if exists(select 1 from esDBforChangeCert.dbo.MeterPasswordChange as tbl where tbl.IsProcessed = 0)
            begin
                raiserror('Не все перекодированнные данные в MeterPasswordChange скопированы в рабочую БД.', 16, 1);
            end;

            set @LastStep = 21;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Перекодированные данные в MeterPasswordChange успешно скопированы в рабочую базу.');
        end;              
        
        if @LastStep = 21
        begin
            set @idRec = null;
            set @idRecMax = null;

            select @idRec = min(q1.ID_Rec), @idRecMax = max(q1.ID_Rec) from esDBforChangeCert.dbo.MeterPasswordChangeCommand as q1 where q1.IsProcessed = 0;
     
            while @idRec <= @idRecMax
            begin
                begin tran;

                update sourc
                set OldPassword = tbl.OldPassword_new,
                    NewPassword = tbl.NewPassword_new
                from dbo.MeterPasswordChangeCommand as sourc
                    inner join esDBforChangeCert.dbo.MeterPasswordChangeCommand as tbl on sourc.ID_Cmd = tbl.ID_Cmd
                where tbl.IsProcessed = 0
                    and tbl.ID_Rec >= @idRec
                    and tbl.ID_Rec < @idRec + @batchSize;

                update tbl
                set IsProcessed = 1
                from esDBforChangeCert.dbo.MeterPasswordChangeCommand as tbl
                where tbl.IsProcessed = 0
                    and tbl.ID_Rec >= @idRec
                    and tbl.ID_Rec < @idRec + @batchSize;

                set @idRec = @idRec + @batchSize - 1;

                commit tran;
            end;

            if exists(select 1 from esDBforChangeCert.dbo.MeterPasswordChangeCommand as tbl where tbl.IsProcessed = 0)
            begin
                raiserror('Не все перекодированнные данные в MeterPasswordChange скопированы в рабочую БД.', 16, 1);
            end;

            set @LastStep = 22;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Перекодированные данные в MeterPasswordChangeCommand успешно скопированы в рабочую базу.');
        end;

        if @LastStep = 22
        begin
            insert into dbo.CertInfo(CertName, PublicKey, PrivateKey)
            select @certName, ci.PublicKey, ci.PrivateKey
            from esDBforChangeCert.dbo.CertInfo as ci 
            where ci.CertName = @certName + '_new';

            if @@rowcount <> 1
            begin
                raiserror('Не удалось скопировать новый сертификат в CertInfo рабочей базы..', 16, 1);
            end;

            set @LastStep = 23;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Новый сертификат скопирован в CertInfo рабочей базы.');
        end;

        if @LastStep = 23
        begin
            exec dbo.CertCreate;

            if not exists(select 1 from sys.certificates as q1  where q1.name = @certName)
            begin
                raiserror('Не удалось создать сертификат в системной таблице на основании нового сертификата в рабочей базе.', 16, 1);
            end;

            set @LastStep = 24;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Новый сертификат создан в sys.certificates рабочей базы.');
        end;

        if @LastStep = 24
        begin
            set @LastStep = 25;
            insert into esDBforChangeCert.dbo.LogChangeCert(ID, Info) values(@LastStep, 'Сертификат успешно пересоздан, данные перекодированы.');

            raiserror('Сертификат успешно пересоздан, данные перекодированы.', 10, 1);
        end;

        if @LastStep >= 1
        begin
            select * from esDBforChangeCert.dbo.LogChangeCert as q1 order by q1.ID;
        end;
    end try
    begin catch
        if @@trancount <> 0
        begin
            rollback;
        end;

        throw;
    end catch;
end;
go