MySQL Forums
Forum List  »  Stored Procedures

Re: procedure with if
Posted by: Peter Brawley
Date: April 18, 2019 09:33AM

Design problems:

1. The sproc seeks to work around the possibility that the target table does not have the structure required by the data manipulation requirement. This is a serious design error. Database structures need to be verified entirely before any data manipulation code runs.

2. The data manipulation this sproc tries to implement could be solved much more simply and effectively with straghtforward sql: insert intoi reg_conb ... select ... from tbl_conbp...

Syntax errors:

1. ...if(cursor_conbp) INSERT INTO... should be ...if cursor_conbp THEN INSERT INTO...

2. In ...UPDATE reg_conbp SET NEW_CONBP_FECHA = v_fecha and NEW_CONBP_MONTO = v_monto, lose the AND.

Logic problems:

1. ...if(cursor_conbp) INSERT INTO... is illogical. If cursor creation failed, the sproc will not reach this line---it will have crashed out with a cursor creation error.

2. The variable getConbp is never set, so if everything else works, the loop will run forever.

3. MySQL will let you use the same name for a label and a variable, but it's bad practice because it invites confusion.

Options: ReplyQuote

Written By
April 17, 2019 02:06PM
Re: procedure with if
April 18, 2019 09:33AM
April 18, 2019 12:35PM
April 18, 2019 01:52PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.