17.4. Executing a Prepared Statement

After a statement has been prepared, it can be executed. If the statement contains any '?' parameter markers, a data value must be supplied for each of them by means of user variables. (General information about user variables is given in Chapter 16, "User Variables.")

To execute a prepared statement, initialize any user variables needed to provide parameter values, and then issue an EXECUTE … USING statement. The following example prepares a statement and then executes it several times using different data values:

 mysql> PREPARE namepop FROM '     '> SELECT Name, Population     '> FROM Country     '> WHERE Code = ?     '> '; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> SET @var1 = 'USA'; Query OK, 0 rows affected (0.00 sec) mysql> EXECUTE namepop USING @var1; +---------------+------------+ | Name          | Population | +---------------+------------+ | United States |  278357000 | +---------------+------------+ 1 row in set (0.00 sec) mysql> SET @var2 = 'GBR'; Query OK, 0 rows affected (0.00 sec) mysql> EXECUTE namepop USING @var2; +----------------+------------+ | Name           | Population | +----------------+------------+ | United Kingdom |   59623400 | +----------------+------------+ 1 row in set (0.00 sec) mysql> SELECT @var3 := 'CAN'; +----------------+ | @var3 := 'CAN' | +----------------+ | CAN            | +----------------+ 1 row in set (0.00 sec) mysql> EXECUTE namepop USING @var3; +--------+------------+ | Name   | Population | +--------+------------+ | Canada |   31147000 | +--------+------------+ 1 row in set (0.00 sec) 

If you refer to a user variable that has not been initialized, its value is NULL:

 mysql> EXECUTE namepop USING @var4; Empty set (0.00 sec)