Hi all,
I have used a substring function inside my stored procedre as
set @dummy1=substring(@dummyid,2);
but when i print and check the value of @dummy1 as
select @dummy1;
i am getting null value for this local variable. i don know how to proceed…
is it the problem related to substring function…? Or in my procedure …?
here is the entire procedure
Code: ( text )
DELIMITER $$
DROP PROCEDURE IF EXISTS zousho.p_signupnew1$$
CREATE DEFINER=root@localhost PROCEDURE p_signupnew1(fname varchar(50), mname varchar(50), lname varchar(50), dob varchar(100), address1 varchar(100), address2 varchar(100), city varchar(50), zipcode varchar(10), state varchar(50),country varchar(50),email varchar(70),phone varchar(70),fax varchar(70),loginid varchar(15),password1 varchar(255),password2 varchar(255),remq varchar(255),rema varchar(255))
BEGIN
declare toinsert int;
declare generatedid char(9) DEFAULT NULL;
declare fname varchar(20);
declare dummyid varchar(20);
declare dummy1 varchar(255) ;
declare store int;
declare userid char(9);
if (select count(*) from tb_user where ff_login_id=loginid) > 0 then
set toinsert=0;
else
set toinsert=1;
end if;
if(toinsert=1) then
select max(ff_userid) into @dummyid from tb_user;
if @dummyid is null then
set @generatedid= ‘u001’;
end if;
else
select max(ff_userid) into @dummyid from tb_user;
set @dummy1=substring(@dummyid,2);
select @dummy1;
set @store=cast(@dummy1 as unsigned) + 1 ;
if @store < 10 then
set @length_2 = 1;
elseif @store < 100 then
set @length_2 = 2;
elseif @store < 1000 then
set @length_2 = 3;
end if;
set @length_3=repeat(0,3 - @length_2);
set @length_4=concat(@length_3,@store);
set @generatedid:= concat(‘u’ ,@length_4);
select @generatedid;
end if;
CREATE TEMPORARY TABLE t_temp4 LIKE tb_user;
insert into t_temp4(ff_userid,ff_u_first_name,ff_login_id,ff_p assword,ff_password1,ff_user_register_date) values (@generatedid,fname,loginid,password1,password2,now());
INSERT INTO tb_user SELECT t_temp4.* FROM t_temp4;
drop table t_temp4;
END$$
DELIMITER ;
please help me … its urgent …