Home Stored Procedures Insert and Update Insert and Update Stored Procedure using LAST_INSERT_ID() Function

Insert and Update Stored Procedure using LAST_INSERT_ID() Function

Procedure Name:
teams_set

Procedure type:
Insert and Update

Input variables:
Eight

Output:
team_id

Uses function:
LAST_INSERT_ID()

The purpose of this stored procedure is to demonstrate using input variables to add or update a single row in a table. If the primary key value passed in is zero, then the procedure will perform an insert, if the value is > 0, then the row is updated. For an insert, the function last_insert_id() returns the new primary key value for the new row in the table.

Tables:

team table

Stored Procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS `team_set`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `team_set`(
#team_id, sport_id, team_type_id, team_abbreviation, team_name, team_description
IN tm_id INT,
IN sprt_id INT,
IN tm_typ_id INT,
IN tm_abbrv varchar(20),
IN tm_nm varchar(100),
IN tm_desc varchar(255),
IN insrt_dt DATETIME,
IN usr_nm VARCHAR(50))
BEGIN
/*****************************************
Spoc Name: team_set
Date created: 07/24/2009
Author: tcblazek
Description: Inserts or Updates a single row in the team table
Input variables: The team table fields
Output: New or existing primary key value from the team table
*****************************************/
DECLARE new_team_id INT;

IF tm_id = 0
THEN
INSERT INTO team
(sport_id,
team_type_id,
team_abbreviation,
team_name,
team_description,
insert_date,
insert_by,
modified_date,
modified_by)
VALUES (
sprt_id,
tm_typ_id,
tm_abbrv,
tm_nm,
tm_desc,
insrt_dt,
usr_nm,
insrt_dt,
usr_nm);
SET new_team_id = (SELECT );
SELECT new_team_id;
ELSEIF tm_id > 0
THEN
UPDATE team
SET
sport_id = sprt_id,
team_type_id = tm_typ_id,
team_abbreviation = tm_abbrv,
team_name = tm_nm,
team_description = tm_desc,
modified_date = insrt_dt,
modified_by = usr_nm
WHERE team_id = tm_id;
END IF;
END $$
DELIMITER ;

Call Stored Procedure

Insert

call team_set(0, 3, 1, 'DEN', 'Denver Nuggets', 'Denver Nuggets Basketball Team', '2009-07-23 10:20:56', 'Admin');

Update

call team_set(7, 3, 1, 'DEN', 'Denver Nuggets', 'Denver Nuggets Basketball Team Rules!', '2009-07-24 12:21:56', 'Admin');

Result Set

Results of sproc call

Sample Data Used

Team Table
team data
Who's Online
We have 27 guests online