Routine parameters cannot be referenced in statements prepared within the routine; see Section 25.8, “Restrictions on Stored Programs” . 

The following example shows a simple stored procedure that, given a country code, counts the number of cities for that country that appear in the city  table of the world  database. The country code is passed using an IN  parameter, and the city count is returned using an OUT  parameter: 

mysql> delimiter // mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT) BEGIN SELECT COUNT(*) INTO cities FROM world.city WHERE CountryCode = country; END// Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> CALL citycount(’JPN’, @cities); -- cities in Japan Query OK, 1 row affected (0.00 sec) mysql> SELECT @cities; +---------+ | @cities | +---------+ | 248 | +---------+ 1 row in set (0.00 sec) mysql> CALL citycount(’FRA’, @cities); -- cities in France Query OK, 1 row affected (0.00 sec) mysql> SELECT @cities; +---------+ | @cities | +---------+ | 40 | +---------+ 1 row in set (0.00 sec) 

The example uses the mysql  client delimiter  command to change the statement delimiter from ;  to // while the procedure is being defined. This enables the ;  delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql  itself. See Section 25.1, “Defining Stored Programs” . 

The RETURNS  clause may be specified only for a FUNCTION , for which it is mandatory. It indicates the return type of the function, and the function body must contain a RETURN value  statement. If the RETURN statement returns a value of a different type, the value is coerced to the proper type. For example, if a function specifies an ENUM  or SET  value in the RETURNS  clause, but the RETURN  statement returns an integer, the value returned from the function is the string for the corresponding ENUM  member of set of SET members. 

The following example function takes a parameter, performs an operation using an SQL function, and returns the result. In this case, it is unnecessary to use delimiter  because the function definition contains no internal ;  statement delimiters: 

mysql> CREATE FUNCTION hello (s CHAR(20)) mysql> RETURNS CHAR(50) DETERMINISTIC RETURN CONCAT(’Hello, ’,s,’!’); Query OK, 0 rows affected (0.00 sec) mysql> SELECT hello(’world’); 

