MYSQL - ERROR 1048 (23000): Column cannot be null

Question

I have a table that i i created an SP to insert data into it.

When i run the SP i get the error in the subject, if i run a manual insert with the same data this works as expected.

Manual insert works

INSERT INTO Leagues (LeagueName, PlatformID, GameID, ActiveStatus, Admin, PointsSys)
    VALUES ('LeagueName', 1, 1, 'Active', 'Test', 1);

Calling SP does not - Returns error 'ERROR 1048 (23000): Column \'LeagueName\' cannot be null'

CALL `tmddg-data`.`SP_CreateLeague`('LeagueName', 1, 1, 'Active', 'Test', 1)

Create Table

CREATE TABLE `Leagues` (
  `LeagueID` int(11) NOT NULL AUTO_INCREMENT,
  `LeagueName` text NOT NULL,
  `PlatformID` int(11) NOT NULL,
  `GameID` int(11) NOT NULL,
  `ActiveStatus` text NOT NULL,
  `Admin` varchar(45) NOT NULL,
  `PointsSys` int(11) DEFAULT '1',
  PRIMARY KEY (`LeagueID`),
  UNIQUE KEY `LeagueID_UNIQUE` (`LeagueID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Create SP

DELIMITER $$
CREATE PROCEDURE `SP_CreateLeague`(
LN TEXT,
PID INT, 
GID INT, 
ASt TEXT, 
ADM VARCHAR(45), 
PS INT)
BEGIN
    DECLARE exit handler for SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
        @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
        SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
        SELECT @full_error;
    END;

    INSERT INTO Leagues (LeagueName, PlatformID, GameID, ActiveStatus, Admin, PointsSys)
    VALUES ( @LN, @PID, @GID, @ASt, @ADM, @PS);
END$$
DELIMITER ;

The value of LeagueName is null when you do an insert, check the value before the insert. — Grumpy, Mar 28 '20 at 08:57
Please review https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql — P.Salmon, Mar 28 '20 at 09:24

score 0 · Accepted Answer · answered Mar 28 '20 at 09:04

0

Remove the @s in the VALUES clause.

...
INSERT INTO ...
            VALUES (LN,
                    PID,
                    GID,
                    ASt,
                    ADM,
                    PS);
...

answered Mar 28 '20 at 09:04

sticky bit

36,626
12
31
42

Ah, thanks for that! totally missed trying that out! – M Webb Mar 29 '20 at 10:19

MYSQL - ERROR 1048 (23000): Column cannot be null

1 Answers1