# Creating and running MySQL stored functions and procedures

> Create and run MySQL stored functions and procedures to reuse SQL logic, with step-by-step examples on a test database.

Source: https://helpcenter.ruachost.com/developers/databases/creating-and-running-mysql-stored-functions-and-procedures/

This guide explains how to create and execute MySQL stored functions and procedures, which can enhance database security, maintain data integrity, and improve performance.

## Stored Functions

Stored functions in MySQL allow you to manipulate and process data efficiently.

## Setting Up a Test Database

1. Create a test database:

```
CREATE DATABASE username_test;
```

2. Select the database:

```
USE username_test;
```

3. Create a `products` table:

```
CREATE TABLE products (
    prod_id INT NOT NULL AUTO_INCREMENT,
    prod_name VARCHAR(20) NOT NULL,
    prod_cost FLOAT NOT NULL DEFAULT 0.0,
    prod_price FLOAT NOT NULL DEFAULT 0.0,
    PRIMARY KEY(prod_id)
);
```

4. Insert sample data:

```
INSERT INTO products (prod_name, prod_cost, prod_price)
VALUES
('Basic Widget', 5.95, 8.35),
('Micro Widget', 0.95, 1.35),
('Mega Widget', 99.95, 140.00);
```

## Creating a Stored Function

Create a function `calcProfit` that calculates profit by subtracting cost from price:

```
DELIMITER $$
CREATE FUNCTION calcProfit(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,2)
BEGIN
  DECLARE profit DECIMAL(9,2);
  SET profit = price - cost;
  RETURN profit;
END$$
DELIMITER ;
```

## Using the Stored Function

Run a query using the function:

```
SELECT *, calcProfit(prod_cost, prod_price) AS profit FROM products;
```

Sample output:

```
+---------+--------------+-----------+------------+--------+
| prod_id | prod_name    | prod_cost | prod_price | profit |
+---------+--------------+-----------+------------+--------+
|       1 | Basic Widget |      5.95 |       8.35 |   2.40 |
|       2 | Micro Widget |      0.95 |       1.35 |   0.40 |
|       3 | Mega Widget  |     99.95 |      140.00|  40.05 |
+---------+--------------+-----------+------------+--------+
```

## Stored Procedures

Stored procedures differ from stored functions in that they must be invoked with the `CALL` statement.

## Creating a Stored Procedure

Example of a basic procedure `procedureTest` that selects product names:

```
DELIMITER $$
CREATE PROCEDURE procedureTest()
BEGIN
  SELECT prod_name FROM products;
END$$
DELIMITER ;
```

**Executing the Stored Procedure**

```
CALL procedureTest() \G
```

If using phpMyAdmin, omit the `\G` at the end.

## More Information

For full details on MySQL stored procedures and functions, see [MySQL Documentation](http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html).