Calling a procedure

The benefit of a stored procedure truly hits you when you call your first stored procedure.

mysql> CALL PARTPRICE( 3, 1, 864);
+-----------+--------------+
| WIDGET_ID | WIDGET_PRICE |
+-----------+--------------+
|         1 |       253.00 |
|         2 |       536.90 |
|         3 |       734.40 |
+-----------+--------------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)

See how simple it is? This is a great way to group together code that is common to many parts of your application so that you can call it from different places in your application with different parameters with just one statement. This also makes it easier to change the code in just one place instead of in several places.

Dropping a procedure

If you made a non-syntactical mistake while creating your first procedure don’t lose heart.

You can just drop that procedure and create a new procedure with the same name.

IF you are not sure if a procedure exists, it is probably a good idea to insert the “IF EXISTS” clause in your DROP PROCEDURE statement. This tells MySQL to only drop a procedure if it existed in the first place. If the procedure does not exist, a warning is generated instead of an error, which can be viewed by typing the “SHOW WARNINGS” command.

mysql> DROP PROCEDURE IF EXISTS PARTPRICE;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP PROCEDURE PARTPRICE
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1289 | PROCEDURE part does not exist |
+---------+------+-------------------------------+
1 row in set (0.00 sec)

Some useful statements

mysql> SHOW CREATE PROCEDURE PARTPRICE;

It shows you the statement used to create the procedure. This is useful to have if you didn’t create the procedure in the first place but want to know how.

| Procedure | Create Procedure
| PARTPRICE | CREATE PROCEDURE `PARTPRICE`(partid INT ,
     Quantity INT,
     price DECIMAL(6,2)
     )
BEGIN
     DECLARE discount_percent DECIMAL(6,2);
     DECLARE discounted_price DECIMAL(6,2);
     SET discount_percent  =  15;
     SET discounted_price = price - discount_percent /100*price;
     IF quantity > 2 THEN
     SET discounted_price = discounted_price - 2.00;
     END  IF;
     UPDATE WIDGET
     SET widget_price = discounted_price WHERE widget_id = partid;
     Select * from widget;
     END
 |
1 row in set (0.00 sec)