Stored Procedure doesn't work as expected
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.