# substring related issue in stored procedure

**URL:** <https://forums.percona.com/t/substring-related-issue-in-stored-procedure/486>\
**Category:** Other MySQL® Questions\
**Created:** [October 10, 2007, 12:41am UTC](https://forums.percona.com/t/substring-related-issue-in-stored-procedure/486 "2007-10-10T00:41:20Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![prakasherd](https://avatars.discourse-cdn.com/v4/letter/p/bb73d2/32.png) [@prakasherd](https://forums.percona.com/u/prakasherd)\
**Post date:** [October 10, 2007, 12:41am UTC](https://forums.percona.com/t/substring-related-issue-in-stored-procedure/486/1 "2007-10-10T00:41:20Z")

</div>

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 &#64;dummy1;

set &#64;store=cast(&#64;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 …