Click here to Skip to main content
14,971,029 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello all,

i have a databse in mysql named as"dbtrial" and in this database i have a table named as"test"
in test table i have only two coloumns "BranchId","BranchName" where BranchId is the primary key
i want tha every time i enter the records the BranchId field should be incremented by 1. for this i write a code which is as follows........


SQL
use dbtrial;
create procedure sp_insert5
(
 in branchid int(11),
in branchname varchar(50)
)
 select max(branchid)+1  into @branchid from test;
insert into test (branchid,branchname)
values(branchid,branchname);
call sp_insert5(
'zeroerror1')



but i got the proble when i run it


(0 row(s)affected)
(0 ms taken)

Error Code : 1062
Duplicate entry '0' for key 'PRIMARY'
(0 ms taken)

i dont want to use autoincrement.
please help me out

thanks and regard
subiya
Posted
Comments
RDBurmon 21-Apr-11 3:46am
   
Could you please provide me details of sp_insert5

1 solution

Two issues I can see.
1). You are trying to assign a value to an 'in' parameter. It's possible that is failing.
2). When the table is empty, the very first select is going to return a NULL value. You are not handling this situation at all. Suggest you change your select to
select max(nvl(branchid,0))+1 into ....
:)
   
Comments
ahsan.subiya 20-Apr-11 8:18am
   
sir i have changed the query bt still there is the same error.
can u gime me the other code for the same thing

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900