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

Background

Let us create another simple Stored Procedure in MySQL.

Unlike our previous try, we will not have the luxury of a system-generated identity key.

 

Lineage

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

 

Code

Table

department

SQL


    create table testdb.departmentIdentityNo
    (

          id               int         not null

        , 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

departmentAddIdAsIdentityNo

Outline

  1. Sanitize Input Data
    • Department
      • If Department Name is null or empty, please raise error
      • Please use Signal State
  2. Set Values
    • id
      • If id is null or 0
        • Get current maximum number
        • Add 1 to max number
  3. Insert Data
  4. Set Return Values
    • idAssigned
      • Set variable idAssigned to persisted ID

SQL


create database if not exists testdb;

use testdb;

DROP PROCEDURE IF EXISTS departmentAddIdAsIdentityNo;

DELIMITER //

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

READS SQL DATA 

MODIFIES SQL DATA

BEGIN

    /* 
    * Seed idNext as passed in id
    */ 
    set @idNext = id;

    /* 
     *  If Department Name is null 
     *  Please raise an error
    */
    if (
               ( name is null)
            or ( name <=> '')
       ) THEN     
        
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Department Name can not be null'
        ;
   
    end if;     

    /* 
    * If department id is null or zero
	* Then get next available id
    */ 
    
    if (
            (
                   ( @idNext is null )
                or ( @idNext <=> 0 )
            )
      ) THEN
          
          begin

              /*
                Get last assigned department ID
              */
              select max(tblD.id)
          
              into   @idNext
              
              from   departmentIdentityNo tblD    
              
              ;
              
              /*
                To Get ID To Assign, please bump by 1
              */
              set @idNext:= coalesce(
                                          @idNext
                                        , 0
                                    ) 
                                    + 1
                                ;

          end;
          
   END IF;
   
   INSERT INTO departmentIdentityNo
   (
          `id`
        , `name`
   )
   select
          @idNext
        , name
   
   ;
   
   /*
    save assigned id to output argument
   */
   set idAssigned := @idNext;
   
   
END//

-- restore the default delimiter ';'
DELIMITER ;     


Invoke


use testdb;

SET @id:= null;
set @name := 'Speech Therapy';
set @idAssigned := null;


DELETE 
FROM  departmentIdentityNo
WHERE name = @name;

call departmentAddIdAsIdentityNo(@id, @name, @idAssigned);

select 
        tblDep.*
    
FROM  departmentIdentityNo tblDep

 

Code Sharing

Github

Gist

DanielAdeniji/departmentAdd.idasIdentityNo.sql
Link

Leave a 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