substring related issue in stored procedure

  • Filter
  • Time
  • Show
Clear All
new posts

  • substring related issue in stored procedure

    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 )

    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))
    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;
    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;

    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,no w());
    INSERT INTO tb_user SELECT t_temp4.* FROM t_temp4;
    drop table t_temp4;


    please help me .. its urgent ...