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)