Bug #20947 Store Procedure
Submitted: 10 Jul 2006 16:59 Modified: 10 Aug 2006 19:33
Reporter: Jakes Potgieter Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.11 OS:Microsoft Windows (Windows XP)
Assigned to: CPU Architecture:Any

[10 Jul 2006 16:59] Jakes Potgieter
Description:
I have a sp that either updates or inserts a record depending on the id that is passed through. If the PersonID = 0 then insert, else just update the details. The problem is that when I try to do the update... it deletes the record. :( 

I have posted this in the forum.

Here is the sp 

CREATE PROCEDURE spSetUserDetails(IN Firstname VARCHAR(255), IN Lastname VARCHAR(255), IN Email VARCHAR(255), IN Username VARCHAR(255), IN Password VARCHAR(255), IN CompanyID VARCHAR(255), IN UserRightsID VARCHAR(255), IN PersonID INTEGER(11)) 
NOT DETERMINISTIC 
SQL SECURITY DEFINER 
COMMENT '' 
BEGIN 
IF PersonID <> 0 
UPDATE Users SET 
Firstname = Firstname, 
Lastname = Lastname, 
Email = Email, 
Username = Username, 
Password = Password, 
CompanyID = CompanyID, 
RightsLevel = UserRightsID 
WHERE UserID = PersonID; 
ELSE 
INSERT INTO 
Users (Firstname,Lastname,Email,Username,Password,DateCreated,Active, 
CompanyID,RightsLevel) 
VALUES(Firstname,Lastname,Email,Username,Password,current_timestamp(),Active, 
CompanyID,RightsLevel); 
END IF; 
END;

How to repeat:
run the sp
[10 Jul 2006 19:33] Valeriy Kravchuk
Thank you for a problem report. Please, send the SHOW CREATE TABLE results for the table used in your procedure, as well as some data for it and real call statement (with parameters) that demonstrates the behaviour you described.
[10 Aug 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".