I just switched from MS SQL 2000/2005 to MySql.
What's wrong with this stored procedure:
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 ;
Basically,
I want the procedure to return Id, ActionDate, EmailAddressExists,
UsernameExists as a result set.
EmailAdressExists needs to be set true or false.
In MS SQL, I could do SELECT @EmailAddressExists = 1 FROM User where
username = 'asdsa';
It looks like that doesn't work in MySql???
The MySql syntax doesn't look very standard. Who the hell came up with
LIMIT instead of TOP?
