• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

MySQL Stored Procedure not inserting record

Dear Experts,
I'm new to MySQL and having difficulty getting my first stored procedure to work. Below is the sproc. When I CALL the sproc in Workbench it doesn't insert a record. The error message for the failure is "Column 'MemberId' cannot be null." I don't see how it can be null when I define it in the Call. Please show me where I'm wrong. Thanks.

CALL:
CALL usp_insert_basic_profile_info (159,'Batman','Darknight','Batcave','Gotham','NY',12345,'USA',1234567890,'batman@i-collaborateinc.com','Male',6,9,2013,'Darkknight');

SPROC:
DELIMITER $$

CREATE DEFINER=`profiler`@`%` PROCEDURE `usp_insert_basic_profile_info`(
      -- Add the parameters for the stored procedure here
      MemberId INT,
      FirstName VARCHAR(15),
      LastName VARCHAR(30),
      Address1 VARCHAR(60),
      City VARCHAR(45),
      StateProvince VARCHAR(45),
      PostalCode VARCHAR(45),
      Country VARCHAR(45),
      PhoneNumber VARCHAR(20),
      eMAIL VARCHAR(45),
      Gender VARCHAR(8),
      MonthBorn INT(3),
      DayBorn INT(3),
      YearBorn INT(5),
      Alias VARCHAR(20)

)
BEGIN

    -- Insert statements for procedure here

      INSERT INTO basic_profile_info(MemberId, FirstName, LastName, Address1, City,StateProvince,PostalCode,Country,PhoneNumber,eMAIL,Gender,MonthBorn,DayBorn,YearBorn,Alias)

      VALUES (@MemberId, @FirstName, @LastName, @Address1, @City,@StateProvince,@PostalCode,@Country,@PhoneNumber,@eMAIL,@Gender,@MonthBorn,@DayBorn,@YearBorn,@Alias);
END
0
bobbellows
Asked:
bobbellows
1 Solution
 
Om PrakashCommented:
Try:

CREATE PROCEDURE usp_insert_basic_profile_info
(
      -- Add the parameters for the stored procedure here
      IN p_MemberId INT,
      IN p_FirstName VARCHAR(15),
      IN p_LastName VARCHAR(30),
      IN p_Address1 VARCHAR(60),
      IN p_City VARCHAR(45),
      IN p_StateProvince VARCHAR(45),
      IN p_PostalCode VARCHAR(45),
      IN p_Country VARCHAR(45),
      IN p_PhoneNumber VARCHAR(20),
      IN p_eMAIL VARCHAR(45),
      IN p_Gender VARCHAR(8),
      IN p_MonthBorn INT(3),
      IN p_DayBorn INT(3),
      IN p_YearBorn INT(5),
      IN p_Alias VARCHAR(20)

)
BEGIN

    -- Insert statements for procedure here

      INSERT INTO basic_profile_info(MemberId, FirstName, LastName, Address1, City,StateProvince,PostalCode,Country,PhoneNumber,eMAIL,Gender,MonthBorn,DayBorn,YearBorn,Alias)

      VALUES (p_MemberId, p_FirstName, p_LastName, p_Address1, p_City,p_StateProvince,p_PostalCode,p_Country,p_PhoneNumber,p_eMAIL,p_Gender,p_MonthBorn,p_DayBorn,p_YearBorn,p_Alias);
END 

Open in new window

0
 
bobbellowsAuthor Commented:
Worked perfect. Thanks. One last thing. Since I'm new to MySQL is there a reference book I can get that would help me get started?
Bob Bellows
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now