开发者

t-sql recurrency procedure problem

I have this code:

CREATE PROCEDURE AddTask
@SessionID uniqueidentifier,
@Login nvarchar(max),
@Uni nvarchar(max),
@Time datetime,
@MaxOverviewTime int,
@Date datetime,
@DoTaskTime int,
@Priority int,
@TaskID int,
@TaskWillBeDoIn datetime output
AS
BEGIN
SET NOCOUNT ON;
/*Algorytm
1. Pobranie wszystkich zadań do tymczasowej tabelki @Tasks, 
    które mogły by się wykonywać w czasie zadania dodawanego
    1.1 Jeżeli nie ma żadnych takich zadań dodać zadanie do tabelki UsersUniTask
2. Sprawdzić czy istnieje jakieś zadanie o niższym lub rónym priorytecie od zadania dodawanego
    - jezeli tak:
        2.1. Wykonać tą procedurę po skończeniu zadania z wyższym priorytetem 
            (PAMIĘTAĆ O 1 SEKUNDZIE WIĘCEJ NIŻ SUMA CZASU I CZASU WYKONYWANIA)
            (SPRAWDZAĆ OD KOŃCA - ZADANIA NAJPÓŹNIEJ SIĘ KOŃCZĄCEGO)
    - jeżeli nie: 
        2.2. Usunąć wszystkie zadania będące w tymczasowej tabelce @Tasks z tabeli UsersUniTask
        2.3. Dodać zadanie do tabelki UsersUniTask
        2.4. Dla wszystkich krotek z tymczasowej tableki @Tasks zastosować tą procedurę
            (MOŻNA BY USTAWIĆ CZAS ZADAŃ NA KONIEC ZADAIA DODANEGO - PAMIĘTAĆ O 1 SEKUNDZIE WIĘCEJ)
3. Pobranie najnowszych zadań
*/

    Declare @NewTaskTime datetime;
    Declare @Count int;
    Declare @Tasks TABLE
    (
        id uniqueidentifier,
        userUniID uniqueidentifier,
        taskID INT,
        [time] datetime,
        doTask int,
        priority int
    )

/*PTK. 1*/
    Insert Into @Tasks (id, UserUniID, taskID, [time], doTask, priority)
      select uut.id, uut.userUniID, uut.taskID, uut.[time], uut.doTask, uut.priority
        from UsersUniTask uut
            inner join UsersUni uu
                on uut.userUniID = uu.id
        where uu.sessionID = @SessionID
            AND uu.Login = @Login
            AND uu.uni = @Uni
            AND (@Date BETWEEN uut.[time] AND DATEADD(SECOND, uut.doTask, uut.[time])
                OR uut.[time] BETWEEN @Date AND DATEADD(SECOND, @DoTaskTime, @Date))
/*KONIEC PTK. 1*/

Select @Count = count(*) from @Tasks
print 'ilość zadań: ' + Convert(nvarchar(max), @Count)

if(@Count = 0)
    begin
        /*PTK. 1.1*/
        print '1.1'
        Insert Into UsersUniTask (id, UserUniID, taskID, [time], doTask, priority)
        (select newid(), uu.id, @TaskID, @Date, @DoTaskTime, @Priority
            from UsersUni uu
            where uu.sessionID = @SessionID
                AND uu.Login = @Login
                AND uu.uni = @Uni)
        set @TaskWillBeDoIn = @Date;
        /*KONIEC PTK. 1.1*/
    end
else
    begin
        print '2'
        select @Count = count(*) 
            from @Tasks
            where priority <= @Priority 
        if(@Count = 0)
            begin
                print '2.2'
                --Dodanie zadania w jego czasie ptk 2.2-2.4
                --PTK. 2.2
                delete from UsersUniTask where id in (select id from @Tasks)
                --KONIEC PTK. 2.2
                --PTK. 2.3
                Insert Into UsersUniTask (id, UserUniID, taskID, [time], doTask, priority)
                (select newid(), uu.id, @TaskID, @Date, @DoTaskTime, @Priority
                    from UsersUni uu
                    where uu.sessionID = @SessionID
                        AND uu.Login = @Login
                        AND uu.uni = @Uni)
                set @TaskWillBeDoIn = @Date;
                --KONIEC PTK. 2.3
                --PTK. 2.4
                DECLARE @id uniqueidentifier;
                DECLARE @uuID uniqueidentifier;
                DECLARE @tID int;
                DECLARE @dtTime int;
                DECLARE @p int;

                set @NewTaskTime = DATEADD(SECOND, @DoTaskTime + 1, @Date) 

                DECLARE AddTaskCursor CURSOR FAST_FORWARD READ_ONLY FOR (SELECT id, userUniID, taskID, doTask, priority FROM @Tasks)
                Open AddTaskCursor

                FETCH NEXT FROM AddTaskCursor INTO @id, @uuID, @tID, @dtTime, @p;
                WHILE @@fetch_status = 0
                BEGIN
                    --TODO ZROBIĆ WYWOŁANIE PROCEDURY

                    FETCH 开发者_StackOverflow中文版NEXT FROM AddTaskCursor INTO @id, @uuID, @tID, @dtTime, @p;
                END
                CLOSE AddTaskCursor;
                DEALLOCATE AddTaskCursor;

                --KONIEC PTK. 2.4
            end
        else
            begin 
                --PTK. 2.1
                print '2.1'
                select @NewTaskTime = DATEADD(SECOND, doTask + 1, [time]) 
                    from @Tasks
                    where priority <= @Priority
                        AND [time] = (select max([time]) from @Tasks where priority <= @Priority)

                EXEC AddTask
                    @SessionID, 
                    @Login, 
                    @Uni,
                    @Time, 
                    @MaxOverviewTime, 
                    @NewTaskTime,
                    @DoTaskTime, 
                    @Priority, 
                    @TaskID,
                    @TaskWillBeDoIn
                --KONIEC PTK. 2.1
            end
    end


/*PTK. 3*/    
  Exec GetTasks 
    @SessionID, 
    @Login, 
    @Uni, 
    @Time, 
    @MaxOverviewTime
/*KONIEC PTK. 3*/
SET NOCOUNT OFF;
END

And i have probelm with section:

else
                begin 
                    --PTK. 2.1
                    print '2.1'
                    select @NewTaskTime = DATEADD(SECOND, doTask + 1, [time]) 
                        from @Tasks
                        where priority <= @Priority
                            AND [time] = (select max([time]) from @Tasks where priority <= @Priority)

                    EXEC AddTask
                        @SessionID, 
                        @Login, 
                        @Uni,
                        @Time, 
                        @MaxOverviewTime, 
                        @NewTaskTime,
                        @DoTaskTime, 
                        @Priority, 
                        @TaskID,
                        @TaskWillBeDoIn
                    --KONIEC PTK. 2.1
                end

This section is always execute, even if the procedure does not print '2.1' (print '2.1').

Procedure add two records, when it should one.

When I comment out:

EXEC AddTask
                            @SessionID, 
                            @Login, 
                            @Uni,
                            @Time, 
                            @MaxOverviewTime, 
                            @NewTaskTime,
                            @DoTaskTime, 
                            @Priority, 
                            @TaskID,
                            @TaskWillBeDoIn

It's works fine.

What i doing wrong??


Is it possible that you added the begin/end in the problematic section after experiencing the problem?

If the begin/end were missing then the else block would only consist of the print command and the SELECT and EXEC would be performed regardless.

Otherwise, I can't imagine how the EXEC would happen and not the PRINT.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜