Solved

MySQL Stored Procedure not inserting record

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

920 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

17 Experts available now in Live!

Get 1:1 Help Now