CODEWITHSUNDEEP
mysqlvariablesstored-procedures
joshuaegclark
joshuaegclark

Reputation: 3921

MySQL StoredProcedure Variable set from select with parameters as conditions

So I'm trying to get the PatientID (primary key, not null, autoincrement) from a patient that I added to the database earlier within a stored procedure using the parameters for FName and LName passed in in my select statement to set the variable, but I'm getting a syntax error. So any help as to why the syntax is failing would be appreciated.

Here's the relevant code, pFName and pLName are parameters passed into the stored procedure.

DECLARE pPolicyHolder INT;
SET pPolicyHolder = (SELECT PatientID FROM Patient WHERE Fname = pFName AND LName = pLName);

And here's the entire procedure. I realize it's probably not the best/cleanest way to do this, but the way the tables are set up is that the MedicalHistory needs to be added before the Patient can be added, and the Patient must be added before the PatientInsurance can be added because of Foreign Key constraints.

DELIMITER //
CREATE PROCEDURE AddNewPatient(
    IN pAllergies TEXT, IN pMedications TEXT, IN pExistingConditions TEXT, IN pMisc       
    TEXT, IN pFName VARCHAR(30), IN pLName VARCHAR(45), IN pGender CHAR(1), IN pDOB 
    DATE, IN pSSN DOUBLE, IN pMedicalHistory INT, IN pPrimaryPhysician INT, IN 
    pInsuranceCompany INT, IN pCoPay INT)
BEGIN
    START TRANSACTION;
         INSERT INTO MedicalHistory(Allergies, Medications, ExistingConditions, Misc) 
         VALUES(pAllergies, pMedications, pExistingConditions, pMisc);
    COMMIT;
        START TRANSACTION;
              INSERT INTO Patient(FName, LName, Gender, DOB, SSN, 
              MedicalHistory,PrimaryPhysician) VALUES(pFName, 
              LName,pGender,pDOB,pSSN,pMedicalHistory,pPrimaryPhysician);
        COMMIT;

       DECLARE pPolicyHolder INT;
       SET pPolicyHolder = (SELECT PatientID FROM Patient WHERE Fname = pFName AND 
                            LName = pLName);
    START TRANSACTION;
       INSERT INTO PatientInsurance(PolicyHolder, InsuranceCompany, CoPay) 
       VALUES(pPolicyHolder, pInsuranceCompany, pCoPay);
    COMMIT;
    END //
DELIMITER ;

The error:

ERROR 1064 (42000): 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 'DECLARE pPolicyHolder INT;
   SELECT PatientID into pPolicyHolder FROM Patient W' at line 15

Upvotes: 0

Views: 10634

Answers (1)

juergen d
juergen d

Reputation: 204854

You need to declare variables at the beginning of your procedure, not in the middle.

CREATE PROCEDURE AddNewPatient(...)
BEGIN
   DECLARE pPolicyHolder INT;  <------declare here

   START TRANSACTION;
   ...
   COMMIT;

   SET pPolicyHolder = (SELECT PatientID FROM Patient WHERE Fname = pFName AND 
                        LName = pLName);
   ...

Upvotes: 2

Related Questions