CODEWITHSUNDEEP
mysqlstored-procedures
user819640
user819640

Reputation: 250

BEGIN INSERT INTO SQL Procedure error

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

Upvotes: 0

Views: 4945

Answers (2)

Kritner
Kritner

Reputation: 13765

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

Upvotes: 1

Neil Hewitt
Neil Hewitt

Reputation: 121

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

Upvotes: 0

Related Questions