stored procedures - 1048 error even when value is given to the columns mysql -
i have table named staff
create table `staff` ( `idstaff` int(11) not null auto_increment, `fname` varchar(45) not null, `lname` varchar(45) not null, `address` varchar(45) not null, `gender` varchar(45) not null, `bloodgroup` varchar(3) not null, `dob` date not null, `mobile_no` varchar(45) not null, `email` varchar(45) not null, `qualification` varchar(45) not null, `department` varchar(45) not null, `designation` varchar(45) not null, `joindate` date not null, `retiredate` date not null, `refname` varchar(45) not null, `mobile` varchar(45) not null, `relationship` varchar(45) not null, primary key (`idstaff`) ) engine=innodb auto_increment=1003 default charset=utf8;
then stored procedure insert data
delimiter $$ create definer=`root`@`localhost` procedure `insert_staffs`( in fnme varchar(45), in lnme varchar(45), in address varchar(45), in gender varchar(45), in bloodgroup varchar(3), in dateob date, in mobilenum varchar(45), in email varchar(45), in edu varchar(45), in department varchar(45), in designation varchar(45), in joindate date, in retiredate date, in refname varchar(45), in refnum varchar(45), in rel varchar(45)) begin insert staff (`fname`, `lname`, `address`, `gender`, `bloodgroup`, `dob`, `mobile_no`, `email`, `qualification`, `department`, `designation`, `joindate`, `retiredate`, `refname`, `mobile`, `relationship`) values (@fnme, @lnme, @address, @gender, @bloodgroup, @dateob, @mobilenum, @email, @edu, @department, @designation, @joindate, @retiredate, @refname, @refnum, @rel ); end$$ delimiter ;
and when call procedure
call insert_staffs('gore','asdf','asdf','m','ab','1980/03/29','safd','asdf','asdf', 'asdf','asdf','2000/01/04','2030/01/03','sdaf','sdaf','sadf');
it throws 1048 error("column 'fname cannnot null'")
but when pass same value in simple insert statement
insert `hospitalmgmt`.`staff` (`fname`, `lname`, `address`, `gender`, `bloodgroup`, `dob`, `mobile_no`, `email`, `qualification`, `department`, `designation`, `joindate`, `retiredate`, `refname`, `mobile`, `relationship`) values ( 'gore','asdf','asdf','m','ab','1980/03/29','safd','asdf', 'asdf','asdf','asdf','2000/01/04','2030/01/03','sdaf','sdaf','sadf');
it inserted.
so , wrong stored procedure ... how can pass values through stored procedure? please help
use parameter without @
sign inside procedure. mean insert statement inside procedure should like
insert staff (`fname`, `lname`, `address`, ) values (fnme, lnme, address, );
Comments
Post a Comment