1

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;
Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for your comment @Shadow But I should have mentioned that i'm using phpMyAdmin where it allows/forces me to tell it what delimiter I am using when creating a procedure - in which I use "//" and even with doing so, my code still won't work. Any ideas on which part is wrong?
It is in the answer: the final end is terminated by what you provided in the delimiter (//).
I suppose... but I guess knowing that doesn't help me since I can't seem to implement it in the above code. I just don't know where I went wrong.
There is nothing to implement. Instead of end; simply write end//
This code never ended up working. I rewrote it differently by grabbing each column data into variables, then inserting into History values of variables. Thank you for making it clear that it was possible to do so.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.