MySQL Forums
Forum List  »  Stored Procedures

Stored Procedure doesn't work as expected
Posted by: Fabian Wallentowitz
Date: March 19, 2014 01:54AM

Hey guys,

I've got a strange problem and hope you are able to help me. ;-)

I have 2 tables in my db:
user - Attributes: id int(11) PK, name varchar(50)
user_displayname - Attributes: user_id int(11) PK, displayname varchar(50)

For those who want to reproduce the create statements:

CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `user_displayname` (
`user_id` int(11) NOT NULL,
`displayName` varchar(50) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


The user table contains the user data. Each user has an id and a name. The user should be able to set a displayname, but only if he wants to. So not every user has an entry in user_displayname.

Now I need a statement that inserts a displayname to user_displayname when the displayname is not provided to another user in user.name or user_displayname.displayname.

My result is: (with filled data)

INSERT INTO user_displayname( user_id, displayName )
( SELECT id, "fabian" FROM user WHERE NOT EXISTS
(SELECT 1 FROM user WHERE name = 'fabian') AND NOT EXISTS
(SELECT 1 FROM user_displayname WHERE displayname = 'fabian')
AND id = 3) ON DUPLICATE KEY UPDATE displayname = 'fabian';

This works so far. It inserts the Displayname fabian with the id 3 in user_displayname (only when it's not provided to antoher user. So the same statement with id = 4 doesn't insert a row in user_displayname.

Now I want to use this statement in a stored procedure. Heres my definition:

DELIMITER $$

CREATE DEFINER = `myuser`@`%` PROCEDURE `SetDisplayName` ( IN `_user_id` INT( 11 ) , IN `_displayName` VARCHAR( 50 ) ) COMMENT 'Sets the displayname of a user' NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY INVOKER
BEGIN
INSERT INTO user_displayname( user_id, displayName ) ( SELECT id, _displayName FROM user WHERE NOT EXISTS

(SELECT 1 FROM user WHERE name = '_displayName') AND NOT EXISTS

(SELECT 1 FROM user_displayname WHERE displayname = '_displayName') AND id = _user_id) ON DUPLICATE KEY UPDATE displayname = '_displayName';

END $$

DELIMITER ;

But now it's going to be weird.
If I now call the SP like this:

call setdisplayname(3, "fabian")

It inserts the row 3, fabian in user_displayname as I expected.

But if I call the SP second time
call setdisplayname(4, "fabian");

It inserts the row 4, fabian too. That is not the result I expected. :/

Do you have any idea why this happens? I don't get why the single statement works as expected but implemented in a SP doesn't work.

EDIT: Running MySQL version: 5.6.11



Edited 1 time(s). Last edit at 03/19/2014 01:56AM by Fabian Wallentowitz.

Options: ReplyQuote


Subject
Views
Written By
Posted
Stored Procedure doesn't work as expected
2542
March 19, 2014 01:54AM


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.