Explanation

An explanation of an example stored procedure

The form presented below is intended to be able to be cut-and-pasted from a text editor into a MySQL interactive command line session - referred to henseforth as the command-line.

DROP   PROCEDURE  My_Stored_Procedure;

If the procedure already exists, dropping the procedure will remove it from the database; otherwise a harmless error will result.

DELIMITER //

Normally, statements entered into the MySQL command-line are terminated by a semi-colon (';'). Even though SQL Stored Procedures should be able to be parsed based on their BEGIN END blocks, the command-line is unable to do this. Therefore, the 'DELIMITER' keyword is used to change the command-line delimiter to '//' so that any semi-colons in the stored procedure won't be recognised as terminating the procedure statement.

CREATE PROCEDURE  My_Stored_Procedure
(
    $Sid         CHAR(64),
    $PARAMETER1  INT,
    $PARAMETER2  TEXT,
    $PARAMETER3  DATETIME,
    $PARAMETER4  ENUM( "THIS", "OR", "THAT" )
)

The 'CREATE PROCEDURE' statement creates the procedure. Once created, to see the content of a procedure you may enter, e.g: "SHOW CREATE PROCEDURE My_Stored_Procedure". Parameter don't need to start with a dollar sign, but I do this to help distinguish between variables and table field names.

BEGIN

The start of the procedure's body is indicated with the BEGIN keyword. If you were defining a function, before this you would specify the return type and potentially other information.

IF @@read_only THEN

    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';

ELSE

I like to detect whether the database is readonly when calling procedures that will try to modify the database. A SIGNAL SQLSTATE statement is similar (but not identical) to throwing an exception in other languages. SQL stored routines can signal various values, '45000' is... (I need to look this up). Generally double 'at' symbols are used to access ?database system variables?

ELSE

    CALL Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );

    IF NOT( "USER" = @idtype OR "ADMIN" = @idtype ) THEN

        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION';

    ELSEIF NOT $PARAMETER1 THEN
    
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_PARAMETERS';
    
    ELSE

        #
        #   Perform whatever you want here
        #

        SELECT * FROM users WHERE USER=$PARAMETER1 ORDER BY given_name;

    END IF;

END IF;

The 'Users_Authorise_Sessionid' procedure is a custom stored procedure that is used to authorise the session identified passed as the first parameter. The IN/OUT parameters @email, @USER, and @idtype type are filled with appropriate values and returned. These values are then used to determine whether the user is authorised to call the procedure, or not. Once authorised, the 'SELECT' statement is processed, which returns the results as a results set.

END

The 'END' keyword indicates the end of the procedure.

//

The '//' here matches the statement delimiter that was set at the beginning of procedure definition.

DELIMITER ;

The end of statement delimiter is reset to be a semi-colon (';').