Solved

MySQL Stored Procedure not inserting record

Posted on 2013-06-09
2
338 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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 …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

813 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now