0

How can I make a function in a MySQL database that can include some queries for insert table1 and select or anything in another table, and get the parameters in a function to the value

DELIMITER $
CREATE FUNCTION registration(username varchar(15), password(15), level_user int(1), name varchar(30), email varchar(30), address varchar(50)) RETURNS int
BEGIN

INSERT INTO users(username, password, level_user, created, modified) VALUES('username', 'password', 'level_user');
INSERT INTO employes(name, email, adrress) VALUES('name','email', 'address');

END $
k0pernikus
  • 60,309
  • 67
  • 216
  • 347
user8322851
  • 23
  • 6
  • Don't use single quote in values mysql see https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql – P.Salmon Aug 20 '17 at 06:27
  • I mean, also how to create multiple inserts with function, is it possible? – user8322851 Aug 20 '17 at 12:28

1 Answers1

0

Apart from the single quote issue you have not provided a data type for password, you have specified more fields for the insert to users than you are providing in values, employes may be a typo, name in the values for employes is not provided and probably should be username and you must provide a return statement even if you don't intend to use it in your code. Personally I would fix this code to check that the user does not already exist and set the return value to something meaningful that I can check post execution.

This code "works"

drop function if exists registration;
delimiter $$


CREATE FUNCTION registration(username varchar(15), password varchar(15), level_user int(1), name varchar(30), email varchar(30), address varchar(50)) 
RETURNS int

BEGIN

INSERT INTO users(name, password) VALUES(username, password);
INSERT INTO e(name, email, address) VALUES(username,email, address);
return 12;
END $$
delimiter ;
P.Salmon
  • 17,104
  • 2
  • 12
  • 19
  • what the meaning of return 12; – user8322851 Aug 29 '17 at 07:02
  • Without a return statement the function will throw sql error(1320) no RETURN found in function and since something has to be returned in the return statement I picked 12 - other than for syntax reasons it serves no purpose. – P.Salmon Aug 29 '17 at 07:07