Primer

An example stored procedure

For a detailed, line-by-line explanation of this example, see the explanation page.

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

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

IF @@read_only THEN

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

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

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

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

    #
    #   Perform whatever you want here
    #

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

END IF;

END
//
DELIMITER ;