Solved

MySQL Stored Procedure not inserting record

Posted on 2013-06-09
2
332 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
Comment Utility
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
Comment Utility
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 Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

11 Experts available now in Live!

Get 1:1 Help Now