Skip to content

Stored Procedures and Functions

Programming SQL — Advanced Difficulty 3/5

Beginning of the lesson

Stored Procedures and Functions

Move reusable logic into the database, and know exactly when that pays off.

Big idea: A stored routine is a named block of SQL saved inside the database and executed on demand. A procedure is invoked with CALL and performs actions (it can return result sets and out-parameters); a function returns a single value and can be used inside an expression such as a SELECT list or a WHERE clause. Both let you package parameters, local variables and control flow (IF, CASE, loops) next to the data they touch.
By the end of this topic you will be able to:
  • write CREATE PROCEDURE and CREATE FUNCTION and handle the MySQL DELIMITER gotcha,
  • use IN, OUT and INOUT parameters correctly,
  • declare local variables and assign them with SET and SELECT ... INTO,
  • drive logic with IF, CASE and the WHILE / REPEAT / LOOP constructs,
  • choose between a procedure and a function, and judge when server-side logic helps and when it hurts.

Continue this lesson on YourAIZone

The full lesson has the complete explanation, worked examples, common mistakes, tests with instant checking and an AI tutor. Start right away as a guest, or create an account to keep your progress.

Continue as a guest Create an account Log in

In the full lesson

  • The complete explanation
  • Worked examples
  • Common mistakes and how to avoid them
  • Test questions: 15
  • AI tutor that answers questions about the topic
  • Progress tracking for students, parents and teachers