I have the following mysql stored procedure:
DELIMITER //
CREATE PROCEDURE INSERT_INTO_WORKDAY(IN deviceName VARCHAR(16),
IN cardUID VARCHAR(14))
BEGIN
SET @projectId = (SELECT MIN(ID) FROM PROJECT WHERE DEVICEID =
(SELECT MIN(ID) FROM DEVICE WHERE NAME = deviceName)
ORDER BY ID DESC LIMIT 1);
SET @workerId = (SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID);
SELECT @workerId;
SET @id = (SELECT MIN(ID) FROM WORKDAY
WHERE WORKERID = @workerId
AND PROJECTID = @projectId
AND STOPTIME IS NULL
AND STARTTIME IS NOT NULL);
IF (@id >= 0)
THEN
UPDATE WORKDAY SET STOPTIME = now() WHERE ID = @id;
ELSE
INSERT INTO WORKDAY (WORKERID, PROJECTID, STARTTIME)
VALUES (@workerId, @projectId, now());
END IF;
END; //
DELIMITER ;
When I call the procedure once, I get the result:
mysql> CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "045D91B22C5E80");
+-----------+
| @workerId |
+-----------+
| 1 |
+-----------+
1 row in set (0.01 sec)
Query OK, 1 row affected (0.09 sec)
From second run, is not working anymore:
mysql> CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "045D91B22C5E80");
+-----------+
| @workerId |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)
ERROR 1048 (23000): Column 'WORKERID' cannot be null
I did try to isolate and run the procedure with only this two lines of code:
SET @workerId = (SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID);
SELECT @workerId;
And it behaves just like above, but if I try to run SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = "045D91B22C5E80";
alone, works without problems.
The @projectId
variable behaves normal everytime I call the stored procedure.
Can someone help me to find a solution?
Thank you!
My setup:
mihai:~$ mysql -V
mysql Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
mysql> describe WORKER;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| ID | int | NO | PRI | NULL | auto_increment |
| FIRSTNAME | varchar(15) | NO | | NULL | |
| LASTNAME | varchar(20) | NO | | NULL | |
| CARDNUMBER | varchar(14) | NO | | NULL | |
| POSITIONID | int | NO | MUL | NULL | |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> SELECT * FROM WORKER;
+----+-------------+-----------+----------------+------------+
| ID | FIRSTNAME | LASTNAME | CARDNUMBER | POSITIONID |
+----+-------------+-----------+----------------+------------+
| 1 | Ionut Mihai | Popescu | 045D91B22C5E80 | 1 |
| 2 | Ilie | Zbagan | 040D7FB22C5E81 | 2 |
| 3 | Ilie | Ungureanu | 043FA8B22C5E80 | 1 |
| 4 | Adrian | Tehanciuc | 04D894B22C5E80 | 2 |
| 5 | Ioan | Bitoanca | 0405C6B22C5E81 | 2 |
+----+-------------+-----------+----------------+------------+
5 rows in set (0.00 sec)
DROP TABLE IF EXISTS `WORKER`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `WORKER` (
`ID` int NOT NULL AUTO_INCREMENT,
`FIRSTNAME` varchar(15) NOT NULL,
`LASTNAME` varchar(20) NOT NULL,
`CARDNUMBER` varchar(14) NOT NULL,
`POSITIONID` int NOT NULL,
PRIMARY KEY (`ID`),
KEY `POSITIONID` (`POSITIONID`),
CONSTRAINT `WORKER_ibfk_1` FOREIGN KEY (`POSITIONID`) REFERENCES `POSITION` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Best Answer
The problem was my declared local variable
id
. After I changed her name, everything worked just fine.My previous answer was:
I don't know what the problem was, but I did found a solution. I wrote:
instead of