MySQL Multiple Commands in a Procedure

Question

For a class assignment I need to move a record from the Checked_Out_Media table to a History table, where the tables are identical. Then I need to delete the record from the Checked_Out_Media Table. I've gotten it down to this:

Create Procedure spMoveToHistory( IN in_UserID char(6), IN in_MediaBarcode char(3) )

BEGIN

    SELECT @NextRow := (MAX(History_TUID) + 1)
    From History;

    Insert into History
        Select @NextRow, COM.User_ID, COM.Media_Barcode,
            COM.Checked_Out_Date, COM.Return_Date
        From Checked_Out_Media COM
        Where COM.User_ID = in_UserID AND
            COM.Media_Barcode = in_MediaBarcode;
END;

The above code seems to work perfectly fine until I run it. It doesn't do anything, good or bad. When I check the procedure after it is created, It shows that there is an error at the End statement. I'm not sure why that only shows up AFTER I create it... Either way, I was hoping someone could clarify why the above doesn't work so I can try to add the below code. Also, the main reason for this post is to ask: Is it even possible to implement more than one transaction in a procedure?

Delete From checked_out_media
    Where User_ID = in_UserID  AND Media_Barcode = in_MediaBarcode;

Answer 1

The problem with your stored procedure is with how the commands are separated from each other. As mysql documentation on defining stored programs says:

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

To redefine the mysql delimiter, use the delimiter command. The following example shows how to do this for the dorepeat() procedure just shown. The delimiter is changed to // to enable the entire definition to be passed to the server as a single statement, and then restored to ; before invoking the procedure. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> // Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

Concerning your 2nd question: you are not executing any transactions in your stored proc, your question is about if you can execute multiple sql commands within a stored procedure. The answer is yes, you can execute multiple sql commands, see above sample code.

Actually, you may want to consider encluding the insert and the delete into a single transaction, but this is a different question.