-1

When i am trying to execute this procedure it is showing an error. 

USE `metro`;
DROP procedure IF EXISTS `transaction`;

DELIMITER $$
USE `metro`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `transaction`(
IN amount_p INT,
IN id_in INT,
IN id_out INT,
IN uidd INT
)
BEGIN

DECLARE uide INT;
DECLARE amt INT;
DECLARE NOW_AMT INT DEFAULT 0;

SELECT `amount` INTO amt FROM metro.wallet WHERE `uid`=uide;
INSERT INTO metro.transactions(`amount_deducted`,`time`,`station_id_in`,`station_id_out`,`uid`)
VALUES (amount_p,now(),id_in,id_out,uid);
SELECT uidd INTO uide;
SELECT uide;
SET NOW_AMT:=@amt+amount_p;

SELECT amt;
UPDATE metro.wallet SET `uid`=uide,`amount`=NOW_AMT;

END$$

DELIMITER ;

wallet is a table with uid and amount column.

Error code 1048, SQL state 23000: Column 'amount' cannot be null Line 1, column 1

Execution finished after 0 s, 1 error(s) occurred.

Execution :

call metro.transaction(112,1,1,1);
Sandeep Rana
  • 3,251
  • 2
  • 24
  • 38
  • One guess is `SET NOW_AMT:=@amt+amount_p;`, the "@amt" references a user variable, not the store proc variable – DBug Nov 22 '15 at 15:26
  • Only Update command cann't be executed @DBug – Sandeep Rana Nov 22 '15 at 15:31
  • 1
    yes. If my suspicion is correct, @amt is null, so that would make NOW_AMT null, so when you try to update amount column with NOW_AMT, it is null, so update fails. – DBug Nov 22 '15 at 15:34
  • if i does .....UPDATE metro.wallet SET `uid`=uide,`amount`=amount_p;...itworks......when i try UPDATE metro.wallet SET `uid`=uide,`amount`=amt;......it says column amount cann't be null.....i hope this will help u understand my problem – Sandeep Rana Nov 22 '15 at 15:38
  • yes you are right @DBug 'amt' is null – Sandeep Rana Nov 22 '15 at 15:38

1 Answers1

0

Perhaps wrap your AMOUNT in a case statement where you can return a default if it is NULL.

SELECT case when `amount` is NULL then '' else `amount` END as `amount` INTO amt FROM metro.wallet WHERE `uid`=uide;
durbnpoisn
  • 4,666
  • 2
  • 16
  • 30
  • if i does .....UPDATE metro.wallet SET `uid`=uide,`amount`=amount_p;...itworks......when i try UPDATE metro.wallet SET `uid`=uide,`amount`=amt;......it says column amount cann't be null.....i hope this will help u understand my problem – Sandeep Rana Nov 22 '15 at 15:37