MySQL Stored Procedure - SELECT - Example

Source code to create and add sql select stored procedure to catalog

The following example is for creating a simple select stored procedure. You can run it through an explicit call from a host language program.

DROP PROCEDURE IF EXISTS `sp_students_SELECT_byPK` 
GO

CREATE PROCEDURE sp_students_SELECT_byPK
     (
        IN   p_student_id                    INT(11)       , 
        OUT  p_password                      VARCHAR(15)   , 
        OUT  p_active_flg                    TINYINT(4)    , 
        OUT  p_lastname                      VARCHAR(30)   , 
        OUT  p_firstname                     VARCHAR(20)   , 
        OUT  p_gender_code                   VARCHAR(1)    , 
        OUT  p_birth_dttm                    DATETIME      
     )
BEGIN 

    SELECT password                      , 
           active_flg                    , 
           lastname                      , 
           firstname                     , 
           gender_code                   , 
           birth_dttm                    
    INTO   p_password                      , 
           p_active_flg                    , 
           p_lastname                      , 
           p_firstname                     , 
           p_gender_code                   , 
           p_birth_dttm                    
    FROM   students
    WHERE  student_id = p_student_id ; 

END 

GO

You will note that just like in a SQL SELECT or a SQL VIEW you do not have to use all of the columns available when creating a stored procedure. Additionally, for this stored procedure I used the primary key of the table as the means of access (the WHERE clause). You can write a stored procedure that uses an alternate key as a means of access.

Executing the sql select stored procedure

Execute sql insert stored procedure

To run the stored procedure you need to supply a value to the student_id variable. The "WHERE" clause in the code above maps this variable to the student_id column in the table.

/***
   IN    p_student_id   INT(11)
   OUT   p_password     VARCHAR(15)
   OUT   p_active_flg   TINYINT(4)
   OUT   p_lastname     VARCHAR(30)
   OUT   p_firstname    VARCHAR(20)
   OUT   p_gender_code  VARCHAR(1)
   OUT   p_birth_dttm   DATETIME
***/

CALL sp_students_SELECT_byPK
   (
      8, 
      @p_password , 
      @p_active_flg , 
      @p_lastname , 
      @p_firstname , 
      @p_gender_code , 
      @p_birth_dttm
   )
GO

SELECT @p_password      AS p_password      , 
       @p_active_flg    AS p_active_flg    , 
       @p_lastname      AS p_lastname      , 
       @p_firstname     AS p_firstname     , 
       @p_gender_code   AS p_gender_code   , 
       @p_birth_dttm    AS p_birth_dttm   
GO

Stored procedure results

p_password    = 'NesbittJ1'
   p_active_flg  = '1'
   p_lastname    = 'Nesbitt'
   p_firstname   = 'Jon'
   p_gender_code = null
   p_birth_dttm  = null