MySQL Forums
Forum List  »  Newbie

What's wrong with this procedure?
Posted by: Dave Adams
Date: December 10, 2006 07:19PM

DELIMITER $$

DROP PROCEDURE IF EXISTS `listing`.`SaveUser` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SaveUser`(Id INT, Username VARCHAR(50), EmailAddress VARCHAR(255), Salutation VARCHAR(10), FirstName VARCHAR(50), LastName VARCHAR(50), Password VARCHAR(50), Status INT)
BEGIN

DECLARE EmailAddressExists BIT;
DECLARE UsernameExists BIT;
DECLARE ActionDate DATETIME;

SET EmailAddressExists = 0;
SET UsernameExists = 0;

SET EmailAddressExists = !ISNULL((SELECT 1 FROM user WHERE user.EmailAddress = EmailAddress AND NOT user.Id = Id));
SET UsernameExists = !ISNULL((SELECT 1 FROM user WHERE user.Username = Username AND NOT user.Id = Id));

IF EmailAddressExists = FALSE AND UsernameExists = FALSE THEN
SET ActionDate = NOW();
IF ID > 0 THEN
UPDATE user SET user.Username = Username, user.EmailAddress = EmailAddress, user.Password = Password, user.Salutation = Salutation, user.FirstName = FirstName, user.LastName = LastName, user.Status = Status, user.Modified = ActionDate WHERE user.Id = Id;
ELSE
INSERT INTO user (Username, EmailAddress, Password, Salutation, FirstName, LastName, Created) VALUES (Username, EmailAddress, Password, Salutation, FirstName, LastName, ActionDate);
SET Id = @@IDENTITY;
END IF;
END IF;

SELECT Id, ActionDate, EmailAddressExists, UsernameExists;

END $$

DELIMITER ;


All I want is to select if the email or username exists and then return a "result" set of what happened (id, email exists, username exists etc).

I'm a newbiew to MYSQL SQL (I just switched from MS SQL 2000/2005)

Options: ReplyQuote


Subject
Written By
Posted
What's wrong with this procedure?
December 10, 2006 07:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.