How do you create a stored function in SQL?

You create a stored function in SQL by using the CREATE FUNCTION statement, defining the function's logic, and then saving it.

Creating a stored function in SQL involves a few steps. First, you need to use the CREATE FUNCTION statement to start the process. This statement tells SQL that you're about to define a new function. The syntax for this statement is: CREATE FUNCTION function_name ([parameter1 [type1], ...]) RETURNS return_datatype.

Next, you need to define the logic of the function. This is done within a BEGIN END block, which contains the SQL code that makes up the function. This code can include any valid SQL statements, and it's where you define what the function actually does. The logic of the function should end with a RETURN statement, which specifies the value that the function will return when it's called.

Here's an example of a simple stored function in SQL:

```
CREATE FUNCTION total_sales (product_id INT)
RETURNS DECIMAL(10,2)
BEGIN
DECLARE total DECIMAL(10,2);
SELECT SUM(price) INTO total FROM sales WHERE product_id = product_id;
RETURN total;
END;
```

In this example, the function total_sales calculates the total sales for a given product. It takes one parameter, product_id, and returns a decimal value. The logic of the function is defined within the BEGIN END block. It declares a variable total, calculates the sum of the price column from the sales table where the product_id matches the parameter, and stores the result in the total variable. Finally, it returns the value of total.

Once you've defined the function, you can save it in the database by running the CREATE FUNCTION statement. After that, you can call the function in your SQL code just like you would call any other function. For example, you could use the total_sales function in a SELECT statement like this: SELECT product_id, total_sales(product_id) FROM products.

Remember, stored functions in SQL are a powerful tool that can simplify your code and improve performance by reducing network traffic between the application and the database.