BEGIN INSERT INTO SQL Procedure error

Question

I have the following procedure, which is to add a record to a table if there isn't already one in the table.

BEGIN 
    IF NOT EXISTS (SELECT * FROM gearLog 
                   WHERE code = @codeI
                   AND signeeID = @signeeIDI
                   AND signoutDate = @signoutDateI
                   AND signbackDate = NULL) 
    BEGIN 
        INSERT INTO gearLog (code, signeeID, signoutDate, signbackDate, committeeOutID, committeeInID, 

warningsGiven)
        VALUES (@codeI, @signeeIDI, @signoutDateI, NULL, @committeeI, NULL, 0) 
    END 
END

For the following table

enter image description here

However when I go to submit it, I get the following error:

MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN INSERT INTO gearLog (code, signeeID, signoutDate, signbackDate, ' at line 7

I don't know what is syntactically wrong with that part though. If anyone could help I would appreciate it.

Here is how it looks in phpMyAdmin enter image description here

why do you have both `sql-server` and `mysql` tagged? – Kritner Jul 21 '15 at 15:36 — Kritner, Jul 21 '15 at 15:36

score 1 · Answer 1 · edited May 23 '17 at 12:06

1

It looks like you've put some sql-server syntax into your mysql query, which is why you're getting some issues.

your query could look more like:

INSERT INTO gearLog (code, signeeID, signoutDate, signbackDate, committeeOutId, committeeInID, warningsGiven)
SELECT *
from ( 
  SELECT @codeI as code, 
          @signeeIDI as signeeId, 
          @signoutDateI as signoutDate, 
          NULL as signbackDate, 
          @committeeI as committeeOutId, 
          NULL as committeeInId, 
          0 as warningsGiven ) as tmpTable
WHERE not exists (
    SELECT * 
    FROM gearLog 
    WHERE code = @codeI
        AND signeeID = @signeeIDI
        AND signoutDate = @signoutDateI
        AND signbackDate is NULL
) LIMIT 1;

updated a few things. I was missing a select in the inner table. Additionally - and this might have been part of the issue before - I was copying your where clause information, and you had AND signbackDate = NULL, you actually need to check for null as AND signbackDate is NULL.

Sorry, I can't seem to get the syntax correct and sqlfiddle doesn't play well with insert statements in the way I'm trying to use them.

Here's some more information on accomplishing what you're trying to do that relies on unique constraints on the table: MySQL INSERT INTO WHERE NOT EXIST

edited May 23 '17 at 12:06

Community

1
1

answered Jul 21 '15 at 15:47

Kritner

12,693
10
45
68

Your answer compiles correctly, but when I submit the query I get the following error 'Operand should contain 1 column(s)' EDIT: Removing the brackets from the select statement fixed it – user819640 Jul 22 '15 at 09:24
EDIT 2: Removing the brackets allow the query to run without error, but it sublimits of blank row into the table. I have verified that the correct values are being passed in the SQL query Eg 0 0000-00-00 NULL 0 NULL – user819640 Jul 22 '15 at 09:48
what do you mean by a "blank row"? You have not null constraints in your schema, your table would not allow for a empty row. – Kritner Jul 22 '15 at 11:37
I mean it submits records like this Eg 0 0000-00-00 NULL 0 NULL instead of something with values. Your edited answer unfortunately now has an error: MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near line 4 – user819640 Jul 23 '15 at 08:56

score 0 · Answer 2 · edited Jul 21 '15 at 15:50

0

BEGIN 
    IF NOT EXISTS (SELECT * FROM gearLog 
                   WHERE code = @codeI
                   AND signeeID = @signeeIDI
                   AND signoutDate = @signoutDateI
                   AND signbackDate = NULL) 

        INSERT INTO gearLog (code, signeeID, signoutDate, signbackDate, committeeOutID, committeeInID, 

warningsGiven)
        VALUES (@codeI, @signeeIDI, @signoutDateI, NULL, @committeeI, NULL, 0) 

END

edited Jul 21 '15 at 15:50

Kritner

12,693
10
45
68

answered Jul 21 '15 at 15:44

Neil Hewitt

1
1

"MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO gearLog (code, signeeID, signoutDate, signbackDate, committeeOutID, ' at line 8" – user819640 Jul 22 '15 at 09:16

BEGIN INSERT INTO SQL Procedure error

2 Answers2