MySQL, like Microsoft SQL Server and Sybase, includes server-side routines called stored procedures (and functions). Stored procedures allow additional programming logic to be included in queries while maintaining good performance.
While stored procedures include full elements of structured programming, you should always consider that SQL databases work best when used using set operations. Beware that even if things like loop structures are available in stored procedures, you should avoid serializing database operations. In most cases, things like loops can be avoided by using well written queries and/or temporary tables.
If your stored procedure does not have any parameters, create the procedure with the procedure name followed by empty parentheses.
drop procedure if exists sp_DBR_hello_world $$ create procedure sp_DBR_hello_world() begin select 'hello world!'; end $$
call sp_DBR_hello_world(); +--------------+ | Hello World! | +--------------+ | Hello World! | +--------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
When you have parameters, use a comma-separated "variablename data type" definition inside the parentheses. It is good practice to differentiate the parameters with a prefix (here 'in_') so the parameters are not conflicting with column names and procedure code will be easier to read.
drop procedure if exists sp_DBR_show_value$$ create procedure sp_DBR_show_value( in_value int ) begin select concat( 'value is ', invalue); end $$
call sp_DBR_show_value(1971); +-------------------+ | Value | +-------------------+ | The value is 1971 | +-------------------+