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);
}

you're going to have to do something more useful with your exception handling than simply returning a boolean value, which is a complete waste of time. Add a logger there that writes the exception to a text file. Right now, without exception handling, all you have left is guesswork. As it is, I do not think this question is answerable without the exception message and stacktrace. — user1666620, Dec 02 '15 at 15:18
Your try can be successful and the stored procedure can still fail for various reasons. Try to catch the return value on the stored proc. — Falanor, Dec 02 '15 at 15:30
how can i handle the return value of BeginExecuteNonQuery(); — farham heidari, Dec 02 '15 at 16:39
The bigger issue I see is why in the world are you using nested while loops for inserts? This could be done set based as a single insert utilizing a tally table instead of all this crazy looping. — Sean Lange, Dec 02 '15 at 16:40
@SeanLange -- haven't you heard?.. everything is better with a loop. 8) — Hogan, Dec 02 '15 at 19:15
There are so many things that could stand to be improved in here. The text datatype has been deprecated for over a decade in favor of varchar(max). Do you really need that many characters anyway? Then you have the splitter, so often those are filled with while loops too instead of a number of better approaches. I could help you get rid of the looping but I would need a LOT more information to do much more than guess. — Sean Lange, Dec 02 '15 at 19:21
@Hogan I suppose if by better we mean slower than yes everything is better with a loop. :S — Sean Lange, Dec 02 '15 at 19:28
@SeanLange -- Not just slower, slower AND harder to maintain. — Hogan, Dec 02 '15 at 20:31
@hogan ,,, what you think if i make a query in asp.net and then executeNonQuery ? — farham heidari, Dec 03 '15 at 07:12
@SeanLange what you think if i make a query in asp.net and then executeNonQuery ? — farham heidari, Dec 03 '15 at 07:12
@farhamheidari I have no idea how changing this to a query in asp.net would have any tangible effect on the result. FIRST you need to rewrite this functionality to not use a loop -- then put it in a SP or not as you wish. — Hogan, Dec 03 '15 at 14:41
Honestly I think the whole procedure is completely flawed and needs a complete redesign. — Sean Lange, Dec 03 '15 at 14:42
i forget this procedure and started to send ajax call for each account and wait for finish request .. just be a little kind with SQL :) — farham heidari, Dec 03 '15 at 17:31

score 0 · Accepted Answer · answered Dec 12 '15 at 15:58

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);
}

Stored procedure don't fire some time

1 Answers1