how to use enum parameters in mysql stored procedure

[shadd]

i have this procedure:

DELIMITER $$
CREATE PROCEDURE sp_add_user(IN inname text,IN insex tinytext)
	MODIFIES SQL DATA
BEGIN
set @newEanswer_sql=CONCAT('INSERT INTO user(fname,sex)values("',inname,'","',insex,'")'  );
			PREPARE snewEans_insert FROM @newEanswer_sql;
			EXECUTE snewEans_insert;
			DEALLOCATE PREPARE snewEans_insert;
	END$$
DELIMITER ;

when i run it like so:

call sp_add_user('pierre','');

it gives me the following error:

RETURNED_SQLSTATE	MESSAGE_TEXT
01000	Data truncated for column 'sex' at row 1

here is table:

create table user
(
	userid	int unsigned not null auto_increment,
	fname  	text  ,
	
	sex  enum('male','female'),

	Primary key(userd)
);

 

how can i solve this??

[requinix]

That procedure is wacky. Why is it trying to make a simple INSERT be so complicated? And so prone to not working?

The error is telling you, in a weird way that doesn't seem very obvious, that the value you used is not valid. Which it clearly isn't.

[shadd]

8 minutes ago, requinix said:

That procedure is wacky. Why is it trying to make a simple INSERT be so complicated? And so prone to not working?

The error is telling you, in a weird way that doesn't seem very obvious, that the value you used is not valid. Which it clearly isn't.

what if i need to account for null entries to this field

 

[requinix]

Don't overthink the procedure so much and just have it do the absolute most obvious thing: an INSERT statement.

[shadd]

13 minutes ago, requinix said:

Don't overthink the procedure so much and just have it do the absolute most obvious thing: an INSERT statement.

SOLVED IT BY ADDING IF INSEX<>''THEN

DO INSERT WITH THE ENUM FIELD

ELSE

DO INSERT WITHOUT THE ENUM FIELD IN QUERY

END IF;

[Barand]

INSERT INTO user (fname,sex) values (inname, NULLIF(insex, '') );