Stored procedure don't fire some time

Question

I have a stored procedure SetMonthlyGroup (code shown below), but there is a little big but not complex it just repeat for each of @Accounts set @Amount in each month repeat for all @monthCount

By the way I tested it and it works.

ALTER PROCEDURE [yeganeh].[SetMonthlyGroup] 
    @Accounts text,
    @StartDate datetime,
    @monthCount int,
    @SanNum int,
    @Amount bigint 
AS
BEGIN
    DECLARE @returnList AS TABLE (Name nvarchar(500),
                                  IDSource int,
                                  id int NOT NULL PRIMARY KEY IDENTITY(1,1)
                                 )
 
    DECLARE @result int
    SET @result = (SELECT COUNT(*) 
                   FROM dbo.splitstring(@Accounts,','))

    INSERT INTO @returnList  
        SELECT *  
        FROM dbo.splitstring(@Accounts,',')

    SET @result = (SELECT COUNT(*) FROM @returnList)
 
    DECLARE @i int
    SET @i = 0

    DECLARE @AccountIndex nvarchar(20)
    DECLARE @monthIndex int
    SET @monthIndex = 0

    DECLARE @payDate datetime
    SET @payDate = getdate()
 
    begin try
    begin transaction
        while @i < @resualt
        begin
            set @i = @i + 1

            select @AccountIndex = Name 
            from @returnList 
            where id = @i

            set @monthIndex = 0

            while @monthIndex < @monthCount
            begin
                set @payDate = (DATEADD(month, @monthIndex, @StartDate))
            
                insert into Sandogh_Monthly 
                values (@SanNum, @AccountIndex, @Amount, 'NotPaid', @payDate) 

                set @monthIndex = @monthIndex + 1
            end
         end
     
         insert into Logs 
         values (@SanNum, 'Monthly', 'System Log', getdate(), 'Transacction Commit', NULL)
         commit
     end try
     begin catch
         rollback

         insert into Logs 
         values (@SanNum, 'Monthly', 'System Log', getdate(), 'Transacction rollback', NULL)
     end catch
END

I execute this stored procedure as a transaction and save the log in log table

Sometimes I execute stored procedure with ADO.net and ExecuteNonQuery returns true, but no row shows up in the log table, and no row in monthly table.

I call it like this:

public bool inserttMonthly(string accounts, int month, DateTime startDate)
{
        DB dbsql = new DB();
        dbsql.AddParams("@Accounts", accounts);
        dbsql.AddParams("@StartDate", startDate);
        dbsql.AddParams("@monthCount", month);
        dbsql.AddParams("@SanNum", this.Mont_SanNum);
        dbsql.AddParams("@Amount", this.Mont_Amount);

        return dbsql.ExecuteWithNoResultDSParamsSP("SetMonthlyGroup");
    }

and db class

public bool ExecuteWithNoResultDSParamsSP(string storedProcedureName)
{
    this.Connect();
    try
    {
        this.cmd.CommandText = storedProcedureName;
        this.cmd.CommandType = CommandType.StoredProcedure;
        this.cmd.Connection = this.cn;
        this.cmd.BeginExecuteNonQuery();
        this.cn.Close();
        return true;
    }
    catch (Exception ex)
    {
        return false;
    }
}

I change db class like following code, and it returns :-

value Id = 1, Status = RanToCompletion, Method = "{null}", Result = "" System.IAsyncResult {System.Threading.Tasks.Task}

 System.IAsyncResult value= this.cmd.BeginExecuteNonQuery();
            string msg = value.ToString();

finally i decide to do it by Ajax . compere new way with store procedure

function setMonth(account ) {
    // $("#Loading").show();
    setTimeout(function () {
        $.ajax({
            url: "../UCSandogh/Ajax/Month.aspx",
            type: "get",
            dataType: "HTML",
            async: false,
            cache: false,
            timeout: 30000,
            data: { action: "setMonth", account: account, date: sDate, amount: moneyAmount, monthCount: monthCountInput },
            error: function () {
                return false;
            },
            success: function (msg) {
                progressBar(parseInt(currentProg));
                if (msg) {
                    lastInsertStatus = true;
                    return true;
                } else {
                    lastInsertStatus = false;
                    return false;
                }
            }
        }).done(NextAcc())
    }, 3000);
}

Answer 1

finally i decide to do it by Ajax . compere new way with store procedure

function setMonth(account ) {
    // $("#Loading").show();
    setTimeout(function () {
        $.ajax({
            url: "../UCSandogh/Ajax/Month.aspx",
            type: "get",
            dataType: "HTML",
            async: false,
            cache: false,
            timeout: 30000,
            data: { action: "setMonth", account: account, date: sDate, amount: moneyAmount, monthCount: monthCountInput },
            error: function () {
                return false;
            },
            success: function (msg) {
                progressBar(parseInt(currentProg));
                if (msg) {
                    lastInsertStatus = true;
                    return true;
                } else {
                    lastInsertStatus = false;
                    return false;
                }
            }
        }).done(NextAcc())
    }, 3000);
}