A stored procedure is a named SQL script saved in a database and run as one unit. It makes a repeated task reusable for permitted users. Unlike a view, it can run multiple queries, accept values through parameters, and return results or output values. section 20.0 section 20.2 section 20.3
For example, a procedure can join title and category data once, then be called whenever that report is needed. In MySQL, multi-statement procedures use BEGIN and END; changing the delimiter temporarily prevents inner semicolons from ending the definition too early. section 20.2.1
DELIMITER // #1
CREATE PROCEDURE GetTitleCategory() #2
BEGIN #3
SELECT #4
t.TitleName,
c.CategoryName
FROM title t
INNER JOIN category c
ON t.CategoryID = c.CategoryID;
END // #5
DELIMITER ; #6
This complete GetTitleCategory definition saves a join as a procedure; calling it returns each title with its category. section 20.2.1
Parameters make one procedure useful for several requests. Define each parameter’s name, data type, and input or output role. A title-name parameter can filter the returned rows; using COALESCE can instead return all titles when the supplied value is NULL. section 20.2.2
Keep procedures manageable:
- Match parameter and variable data types to the columns they are compared with. section 20.2.3
- Use clear names and descriptive comments, especially when multiple queries are involved. section 20.2.3
- Avoid deeply nesting procedures, because troubleshooting can become difficult. section 20.2.3
- Check product-specific syntax: SQLite does not support stored procedures, and calling syntax differs among database systems. section 20.2.1
Fetching one order
GetOrder accepts an OrderID, reads a view that combines order headers and items, joins titles, and returns the order date, title, quantity, and item price. An argument can be a literal such as 1049 or a variable. section 20.4 section 20.5
Procedures can also write data and make decisions. AddPromotion inserts a promotion only when its PromotionCode parameter is not NULL. More involved procedures may use variables, cursors, loops, and handlers to process annual payments. section 21.2.1 section 23.2.1
