Member Avatar for satya2mysql 
DELIMITER $$

DROP PROCEDURE IF EXISTS `hrprocess_temp`.`sp_alltabinsert1` $$
CREATE DEFINER=`invensis`@`%` PROCEDURE `sp_alltabinsert1`(empid varchar(10),fname varchar(45),lname varchar(45),
fathername varchar(30),dob varchar(20),gender varchar(6),maritalstatus varchar(9),spousename varchar(15),children varchar(8),
commadd varchar(250),permadd varchar(250),mobileno varchar(15),landlineno varchar(15),email varchar(40),
languages varchar(25),nationality varchar(25),bloodgroup varchar(5),drivingno varchar(20),panno varchar(15),
passportno varchar(15),imagepath varchar(100),resumepath varchar(1000),skill varchar(250),deptname varchar(30),
Course varchar(20),Specialize varchar(35),institute varchar(25),PassYear varchar(10),
Organization varchar(25),Designation varchar(25),FromDt varchar(15),ToDt varchar(15),ReasonsChange varchar(100))
begin
declare empno int;
insert into Employee   (Emp_Id,First_Name,Last_name,Father_Name,Date_Birth,Gender,Marital_Status,Spouse_Name,
Children_No,Comm_Add,Perm_Add,Mobile_No,Land_No,Email_id,Language,Nationality,Blood_Group,Passport_No,
Pan_No,Driving_No,Image,Resume,skills ,Dept_Name)
                                      values (empid,fname,lname,fathername,dob,gender,maritalstatus,spousename,children,commadd,
                                       permadd,mobileno,landlineno,email,languages,nationality,bloodgroup,drivingno,panno,passportno,
                                        imagepath,resumepath,skill,deptname);

set@empno= last insert Emp_No();

 insert into Education ( Course, Specialize,institute,Pass_year,Emp_No)
                     values(course,specialize,institute,passyear,@empno);

insert into Experience (Organization,Designation,From_Dt,To_Dt,Reasons_Change,Emp_No)
                     values (Organization,Designation,FromDt,ToDt,ReasonsChange,@empno);
end $$

DELIMITER ;
Edited by peter_budo because: Keep It Clear - Do wrap your programming code blocks within [code] ... [/code] tags
  • 4 Contributors
  • 3 Replies
  • 180 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by peter_budo

Dani AI

Generated

Short answer: capture the auto-generated Emp_No immediately after the Employee INSERT and reuse that value. As and pointed out, MySQL provides a session-scoped function that returns the last AUTO_INCREMENT value generated on the current connection; call it right after your INSERT and store it in the procedure variable you already declared. MySQL manual: LAST_INSERT_ID()

Inside a stored procedure use your local variable (you have declare empno int;) and assign it from the function before any other statements run. For example, immediately after the INSERT use either SET empno = LAST_INSERT_ID(); or SELECT LAST_INSERT_ID() INTO empno; and then use empno in the Education and Experience INSERTs. Using a local variable keeps the value scoped to the routine; using @empno would be a session (user) variable. See a common stored-proc pattern here. (StackOverflow example and the manual show this usage.) stored-proc example MySQL manual: LAST_INSERT_ID()

Troubleshooting and gotchas: if you explicitly insert a value into the auto-increment column (or if the INSERT fails) there’s no generated id to return. For multi-row INSERT the function returns the first generated id (not every id). Triggers/functions can temporarily change LAST_INSERT_ID() (functions/triggers restore it; procedures do not), and certain error/rollback cases make the value undefined — so read it immediately after the INSERT. MySQL AUTO_INCREMENT notes MySQL manual: LAST_INSERT_ID()

Practical fix for your procedure (): remove the incorrect set@empno= last insert Emp_No();, replace it with one of the two assignments above immediately after the Employee INSERT, then reuse the local empno in the following INSERTs. If the three inserts must succeed or fail together, wrap them in a transaction (START TRANSACTION / COMMIT) so partial work isn’t left behind.

Recommended Answers

All 3 Replies

Member Avatar for smantscheff

You are probably looking for the mysql function LAST_INSERT_ID()

Member Avatar for pritaeas

Try LAST_INSERT_ID()

Member Avatar for peter_budo

Threads merged, please do not multipost in the future

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.