title: MySQL StoredProcedure Variable set from select with parameters as conditions
body: <p>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.</p>
  
  <p>Here's the relevant code, pFName and pLName are parameters passed into the stored procedure.</p>
  
  <pre><code>DECLARE pPolicyHolder INT;
  SET pPolicyHolder = (SELECT PatientID FROM Patient WHERE Fname = pFName AND LName = pLName);
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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 ;
  </code></pre>
  
  <p>The error:</p>
  
  <pre><code>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
  </code></pre>