> ## Documentation Index
> Fetch the complete documentation index at: https://kb.hosting.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Creating and running MySQL stored functions and procedures

> Master MySQL stored functions and procedures on Hosting.com with our detailed guide, featuring instructions, code snippets, and related articles.

This article describes how to create and execute MySQL stored functions and procedures on your hosting.com account. You can use stored functions and procedures for a wide range of scenarios. For example, well-designed stored functions and procedures can enhance database security, improve data integrity, and increase performance.

## Stored functions

MySQL stored functions provide a powerful and flexible way to manipulate and process data. You can define and run stored functions on any hosting.com server that uses MySQL (or MariaDB, a drop-in replacement for MySQL).

### Setting up a test database

To demonstrate a basic example of stored functions, let's start by creating a database that we can use for testing purposes. In the following SQL statement, replace *username* with your account username:

```sql theme={null}
CREATE DATABASE username_test;
```

<Note>
  You can run the previous SQL command (and the following SQL commands) from the command line [using the MySQL tool](/docs/connect-to-mysql-from-the-command-line), or in your web browser [using phpMyAdmin](/docs/phpmyadmin-and-phppgadmin).
</Note>

If you are using phpMyAdmin, click the name **username\_test** to select the database. Otherwise, if you are using the mysql command-line program, type the following SQL statement:

```sql theme={null}
USE username_test;
```

Next, create a table in the **username\_test** database named **products**. To do this, run the following SQL statement:

```sql theme={null}
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));
```

The following SQL statement adds some sample data to the **products** table:

```sql theme={null}
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 the stored function

Now that we have a database and a table to work with, we are ready to create a stored function. Let's create a function named **calcProfit**. This function takes two input parameters: the cost and the price of something. It calculates the profit by subtracting the cost from the price, and then returns the value to the calling expression.

To create this stored function, run the following MySQL statements:

```sql theme={null}
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 ;
```

<Note>
  The **DELIMITER** command at the beginning of these statements prevents MySQL from processing the function definition too soon. The **DELIMITER** command at the end of these statements returns processing to normal.
</Note>

### Using the stored function

You can now execute the stored function in a database query. The following SQL statement demonstrates how to do this:

```sql theme={null}
SELECT *, calcProfit(prod_cost,prod_price) AS profit FROM products;
```

This SQL statement returns the following results:

```
+---------+--------------+-----------+------------+--------+
| 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 |  40.05 |
+---------+--------------+-----------+------------+--------+
```

As you can see, the **calcProfit** function automatically calculates the profit (price minus the cost) for each product in the table.

## Stored procedures

Stored procedures are sometimes confused with stored functions, but they are different in some important ways. Stored procedures, for example, must be invoked with the **CALL** statement, whereas stored functions can be used directly in SQL expressions. You can define and run stored procedures on any hosting.com server that uses MySQL.

The following MySQL statements demonstrate how to create a very basic stored procedure named **procedureTest**. This procedure performs a simple lookup on the **products** table that we used in the stored function example above. Although this procedure does not have much practical use, it demonstrates the correct syntax and structure for declaring a stored procedure:

```sql theme={null}
DELIMITER $$
CREATE PROCEDURE procedureTest()
BEGIN
  SELECT prod_name FROM products;
END$$
DELIMITER ;
```

To execute the stored procedure, use the following MySQL statement:

```sql theme={null}
CALL procedureTest() \G
```

<Note>
  If you are using phpMyAdmin, type the previous MySQL statement without the **\G** option at the end.
</Note>

## More information

For more information about stored procedures and functions in MySQL, please visit [http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html](http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html).

## Related articles

* [MySQL triggers](/docs/mysql-triggers)

* [MySQL views](/docs/mysql-views)

* [Connecting to MySQL from the command line](/docs/connect-to-mysql-from-the-command-line)

* [Connecting to MySQL using PHP](/docs/connect-to-mysql-using-php)

* [Managing MySQL databases, users, and tables from the command line](/docs/managing-mysql-databases-and-users-from-the-command-line)