MySQL Stored Procedure with IF/THEN/ELSE

Question

I have a MySQL stored procedure and in it, the following WHILE statement.

I have confirmed that @RowCnt is 1, and @MaxRows is 6090, however after further debugging, I realized that the WHILE statement is going through a single iteration and not continuing; so I'm hoping to have some light shed on what could possibly be causing this.

Full disclosure: I ported this from SQL Server to a MySQL stored procedure, something I have never taken on before. (meaning SQL Server, porting OR stored procedures..)

WHILE @RowCnt <= @MaxRows DO
    SELECT @currentReadSeq:=ReadSeq, @currentReadStrength:=ReadStrength, @currentReadDateTime:=ReadDateTime, @currentReaderID:=ReaderID FROM tblTempRead WHERE rownum = @RowCnt;

    IF ( ((@lastReadSeq + 10) > @currentReadSeq) AND (@lastReaderId = @currentReaderId) ) THEN 
        SET @lastReadSeq = @currentReadSeq, @lastReadStrength = @currentReadStrength, @lastReadDateTime = @currentReadDateTime, @lastReaderID = @currentReaderID;
    ELSE
        INSERT INTO tblreaddataresults (SiteID, ReadDateTimeStart, ReadDateTimeEnd, ReadSeqStart, ReadSeqEnd, ReaderID, DirectSeconds) VALUES ('1002', @saveReadDateTime, @lastReadDateTime, @saveReadSeq, @lastReadSeq, @lastReaderID, timestampdiff(SECOND,@saveReadDateTime,@lastReadDateTime));

        SET @saveReadSeq = @currentReadSeq, @saveReadStrength = @currentReadStrength, @saveReadDateTime = @currentReadDateTime, @saveReaderID = @currentReaderID;

        SET @lastReadSeq = @saveReadSeq, @lastReadStrength = @saveReadStrength, @lastReadDateTime = @saveReadDateTime, @lastReaderID = @saveReaderID;
    END IF;

    SET @RowCnt = @RowCnt+1;
END WHILE;

Try setting up a continue handler to see if one of your queries gives error, and if that is triggered you know what is wrong. dev.mysql.com/doc/refman/5.0/en/declare-handler.html You can debug SP calls by inserting into dummy tables. — Pentium10
– Pentium10, Commented Jul 19, 2011 at 18:14
The WHILE is running once, and hitting the SET just below the IF statement. It quits after running once. — edwardrbaker
– edwardrbaker, Commented Jul 19, 2011 at 18:44

Ankit · Accepted Answer · 2011-12-02 17:58:14Z

Try This Construct

WHILE (@RowCnt <= @MaxRows)
BEGIN
    SELECT @currentReadSeq:=ReadSeq, @currentReadStrength:=ReadStrength, @currentReadDateTime:=ReadDateTime, @currentReaderID:=ReaderID FROM tblTempRead WHERE rownum = @RowCnt;

    IF (((@lastReadSeq + 10) > @currentReadSeq) AND (@lastReaderId = @currentReaderId))
        BEGIN
        SET @lastReadSeq = @currentReadSeq, @lastReadStrength = @currentReadStrength, @lastReadDateTime = @currentReadDateTime, @lastReaderID = @currentReaderID;
        END
    ELSE
        BEGIN
        INSERT INTO tblreaddataresults (SiteID, ReadDateTimeStart, ReadDateTimeEnd,ReadSeqStart, ReadSeqEnd, ReaderID, DirectSeconds) VALUES ('1002',@saveReadDateTime, @lastReadDateTime, @saveReadSeq, @lastReadSeq, @lastReaderID,timestampdiff(SECOND,@saveReadDateTime,@lastReadDateTime));
        SET @saveReadSeq = @currentReadSeq, @saveReadStrength = @currentReadStrength, @saveReadDateTime = @currentReadDateTime, @saveReaderID = @currentReaderID;
        SET @lastReadSeq = @saveReadSeq, @lastReadStrength = @saveReadStrength,@lastReadDateTime = @saveReadDateTime, @lastReaderID = @saveReaderID;
        END
    SET @RowCnt = @RowCnt+1;
END

Collectives™ on Stack Overflow

MySQL Stored Procedure with IF/THEN/ELSE

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related