---

SET XACT_ABORT ON;
BEGIN TRAN;

DECLARE @tip nvarchar(100), @kod int;
DECLARE @types TABLE (tip nvarchar(100));

INSERT INTO @types VALUES (N'certDUP'), (N'certERR');

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT tip FROM @types;

OPEN c;
FETCH NEXT FROM c INTO @tip;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM dbo.spr_tipSend WITH (UPDLOCK, HOLDLOCK)
        WHERE tip = @tip
    )
    BEGIN
        IF COLUMNPROPERTY(
            OBJECT_ID(N'dbo.spr_tipSend'), N'kod', 'IsIdentity'
        ) = 1
        BEGIN
            INSERT dbo.spr_tipSend
                (tip, notet, subjectEm, body,
                 mapingTxt, fromEm, mapingTip)
            VALUES
                (@tip, N'Письмо МТС',
                 N'Текст формируется функцией dbo.MTS_StaticMail',
                 N'Текст формируется функцией dbo.MTS_StaticMail',
                 N'WR_mtsPublishMail_v13', N'Dasha',
                 N'WR_mtsPublishMail_v13');
        END
        ELSE
        BEGIN
            SELECT @kod = ISNULL(MAX(kod), 0) + 1
            FROM dbo.spr_tipSend WITH (UPDLOCK, HOLDLOCK);

            INSERT dbo.spr_tipSend
                (kod, tip, notet, subjectEm, body,
                 mapingTxt, fromEm, mapingTip)
            VALUES
                (@kod, @tip, N'Письмо МТС',
                 N'Текст формируется функцией dbo.MTS_StaticMail',
                 N'Текст формируется функцией dbo.MTS_StaticMail',
                 N'WR_mtsPublishMail_v13', N'Dasha',
                 N'WR_mtsPublishMail_v13');
        END;
    END;

    UPDATE dbo.spr_tipSend
    SET fromEm = N'Dasha',
        mapingTxt = N'WR_mtsPublishMail_v13',
        mapingTip = N'WR_mtsPublishMail_v13',
        subjectEm = N'Текст формируется функцией dbo.MTS_StaticMail',
        body = N'Текст формируется функцией dbo.MTS_StaticMail'
    WHERE tip = @tip;

    FETCH NEXT FROM c INTO @tip;
END;

CLOSE c;
DEALLOCATE c;

COMMIT;