MySQL:- Stored Procedure – Simple Insert Statement w/ Identity Key

Background

Let us create a simple Stored Procedure in MySQL.

 

Code

Table

department

SQL



    create table testdb.department
    (
    
          id             int         not null
                                     AUTO_INCREMENT
                                     
        , name           varchar(120) not null
        

        , tsAdded          timestamp   not null
                           default     CURRENT_TIMESTAMP
                           
        , tsModified       timestamp   null 
                           ON UPDATE   CURRENT_TIMESTAMP
                           
        , comment          varchar(300) null
   
        , primary key 
          (
            id
          )


        
        , unique index 
          (
            name
          ) 
          
    )
    ;

Stored Procedure

departmentAdd

Outline

  1. Add Department
    • Arguments
      • Department Name ( name )
  2. Insert Record
  3. Capture Identity Value (  LAST_INSERT_ID() )
  4. Return Assigned identity value as idAssigned

SQL


create database if not exists testdb;

use testdb;

DROP PROCEDURE IF EXISTS departmentAdd;

DELIMITER //

CREATE PROCEDURE departmentAdd 
(
      in  name        varchar(60)
    , out idAssigned  int 
) 

MODIFIES SQL DATA

BEGIN

   INSERT INTO department
   (
        `name`
   )
   values
   (
        name
   )
   ;

   -- Get last assigned identity value
   set idAssigned = LAST_INSERT_ID();
   
END//

-- restore the default delimiter ';'
DELIMITER ;     

Invoke


set @name := 'English';
set @idAssigned := null;

delete from department where id > -1;

call departmentAdd(@name, @idAssigned);

set @name := 'Math';
set @idAssigned := null;

call departmentAdd(@name, @idAssigned);

select *
from   department;

select 
        @idAssigned as 'departmentID.lastAssigned'


Output

Output – Department

Output – Department – Last Added

 

Code Sharing

Github

Gist

DanielAdeniji/departmentAdd.IdAsIdentity.Yes.sql
Link

One thought on “MySQL:- Stored Procedure – Simple Insert Statement w/ Identity Key

Leave a Reply to MySQL:- Stored Procedure – Insert Statement w/o Identity Key | Learning in the Open Cancel reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s