Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MySQL Stored Procedure not inserting record

Posted on 2013-06-09
2
343 Views
Last Modified: 2013-06-11
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
Comment
Question by:bobbellows
2 Comments
 
LVL 22

Accepted Solution

by:
Om Prakash earned 500 total points
ID: 39233829
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
 

Author Closing Comment

by:bobbellows
ID: 39238780
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question