Bug #20947 Store Procedure

• Submitted: 10 Jul 2006 16:59
• Modified: 10 Aug 2006 19:33
• Reporter: Jakes Potgieter
• Status: No Feedback
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.0.11
• OS: Microsoft Windows (Windows XP)
• 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".