By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,880 Members | 2,443 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,880 IT Pros & Developers. It's quick & easy.

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

P: n/a
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?

Dec 11 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
ob***********@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
Dec 14 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.