CODEWITHSUNDEEP
mysqlstored-proceduresinsert
bnjmn.myers
bnjmn.myers

Reputation: 439

MYSQL StoredProcedure Insert Issues

I have stored procedure where I'm wanting to insert a student, insert a guardian and then take the Ids from each and insert a record into an assoc table.

Below is the code I'm using but I'm getting the following error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @studentId = LAST_INSERT_ID() INSERT INTO TblPersons ( firstName, lastName' at line 40

CREATE PROCEDURE usp_InsertStudentAndGuardian(
IN p_firstName VARCHAR(45),
IN p_lastName VARCHAR(45),
IN p_gender VARCHAR(1),
IN p_birthDate DATE,
IN p_joinDate DATE,
IN p_isActive BIT,
IN p_isUnderAge BIT,
IN p_gFirstName VARCHAR(45),
IN p_gLastName VARCHAR(45),
IN p_gGender VARCHAR(1),
IN p_gBirthDate DATE,
IN p_gJoinDate DATE,
IN p_gIsActive BIT,
IN p_gIsUnderAge BIT,
IN p_personType INT
)
BEGIN

INSERT INTO TblPersons
(
firstName,
lastName,
gender,
birthDate,
joinDate,
isActive,
isUnderAge
)
VALUES
(
p_fistName,
p_lastName,
p_gender,
p_birthDate,
p_joinDate,
p_isActive,
p_isUnderAge
)
SET @studentId = LAST_INSERT_ID() <------ LINE 40

INSERT INTO TblPersons
(
firstName,
lastName,
gender,
birthDate,
joinDate,
isActive,
isUnderAge
)
VALUES
(
p_gFirstName,
p_gLastName,
p_gGender,
p_gBirthDate,
p_gJoinDate,
p_gIsActive,
p_gIsUnderAge
)
SET @guardianId = LAST_INSERT_ID()

INSERT INTO AtbStudentsGuardians
(@studentId, @guardianId)

INSERT INTO AtbPersonsPersonTypes
(@studentId, p_personType);

END//

Upvotes: 0

Views: 59

Answers (1)

Rahul
Rahul

Reputation: 77926

Terminate every SQL statement with ; else it will get considered as single statement like below

INSERT INTO TblPersons
(
firstName,
lastName,
.......
)
VALUES
(
p_fistName,
p_lastName,
......
);
SET @studentId := LAST_INSERT_ID();

Again, I think your code line below

SET @studentId = LAST_INSERT_ID(); <------ LINE 40

Should be

SET @studentId := LAST_INSERT_ID();

Upvotes: 1

Related Questions