GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

substring related issue in stored procedure

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • 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 )
    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,no w());
    INSERT INTO tb_user SELECT t_temp4.* FROM t_temp4;
    drop table t_temp4;
    END$$

    DELIMITER ;




    please help me .. its urgent ...
Working...
X