registerWithCredentials() Stored Procedure

2 Views

I have created a stored procedure in MysQL for my WordPress Database. The purpose of this procedure is to register a new user into wordpress database using credentials(user_login,user_pass,user_nicename,user_email,and displayname.)
The sql syntax of this procedure is ok, but when i call the procedure i cant get anything in response, something that will tell if the new user is added into database. I dont know what the problem is because when i check into database after calling the procedure, i see no changes made. 

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `registerWithCredentials`(IN in_userlogin VARCHAR(50), IN in_userpass VARCHAR(32),IN in_usernicename VARCHAR(50) ,IN in_useremail VARCHAR(50), IN in_displayname VARCHAR(50))
proc_main:BEGIN
BEGIN
DECLARE ID int(10);
DECLARE CheckExists INT;

SET CheckExists=0;

select count(*) into CheckExists from wp_users where user_login like in_userlogin or user_email like in_useremail;
if (CheckExists>0)
then 

  leave proc_main;
end if;


if (in_userpass is not null or in_userpass !='') and (in_useremail is not null or in_useremail !='')
then
  insert into prod_huselbrand_db.wp_users (
    user_login
    ,user_pass
    .user_nicename
    ,user_email
    ,display_name
  ) VALUES (
    in_userlogin  -- username - IN varchar(50)
    -- email - IN varchar(50)
    ,MD5(in_userpass)   -- passwd - IN blob , 
   ,in_usernicename
,in_useremail
,in_displayname
  );
else
  leave proc_main;
end if;
set ID:=LAST_INSERT_ID(); 

end;
END proc_main
Post Views: 2

Related posts

Leave a Reply

1 comment

  1. I think you need to make a select in the end of your code so you can output something from your stored procedure. In your case I would output the data of the new registered user. For example:

    -- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `registerWithCredentials`(IN in_userlogin VARCHAR(50), IN in_userpass VARCHAR(32),IN in_usernicename VARCHAR(50) ,IN in_useremail VARCHAR(50), IN in_displayname VARCHAR(50))
proc_main:BEGIN
BEGIN
DECLARE ID int(10);
DECLARE CheckExists INT;

SET CheckExists=0;

select count(*) into CheckExists from wp_users where user_login like in_userlogin or user_email like in_useremail;
if (CheckExists>0)
then 

  leave proc_main;
end if;


if (in_userpass is not null or in_userpass !='') and (in_useremail is not null or in_useremail !='')
then
  insert into prod_huselbrand_db.wp_users (
    user_login
    ,user_pass
    .user_nicename
    ,user_email
    ,display_name
  ) VALUES (
    in_userlogin  -- username - IN varchar(50)
    -- email - IN varchar(50)
    ,MD5(in_userpass)   -- passwd - IN blob , 
   ,in_usernicename
,in_useremail
,in_displayname
  );
else
  leave proc_main;
end if;
set ID:=LAST_INSERT_ID();

select ID, user_login, user_pass, user_nicename, user_email, display_name 
      from prod_huselbrand_db.wp_users where ID = ID;

end;
END proc_main