MySql From MS SQL - What"s wrong with this stored procedure?

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?

Re: MySql From MS SQL - What"s wrong with this stored procedure?

objectmodelol@hotmail.com wrote:

> 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?
>

What version of MySQL?
what error do you get when you execute it?


--
Michael Austin.
Database Consultant