0

I'm trying to create a procedure, it worked an hour ago and now it's throwing an error. I exported the procedure(copied a text statement phpmyadmin output for me) and it wont let me execute the code I exported. The exported code is below followed by the error report phpmyadmin is displaying.

Also, all the sql queries work on their own but when the procedure was being called before I exported it, it was running but the queries were not affecting any rows. Am I labeling the variables wrong or something?(I don't think I am, I did check)

What I entered:

CREATE DEFINER=`root`@`localhost` PROCEDURE `DeleteCard`(IN `@aName` VARCHAR(255))
NO SQL
BEGIN
UPDATE Decks 
JOIN Amount ON amount.DeckName = decks.DeckName 
SET decks.DeckTotal = Decks.DeckTotal - Decks.DeckTotal
WHERE Amount.AmountName = @aName;

UPDATE Types t1
JOIN Cards ON cards.TypeName = t1.TypeName 
JOIN Amount ON amount.CardName = Cards.CardName
SET t1.TypeTotal = t1.TypeTotal - Amount.Amount
WHERE Amount.CardName = @aName;

DELETE  
FROM Amount 
WHERE CardName = @aName;


DELETE  
FROM Cards 
WHERE CardName = aName;
END

Error report:

CREATE DEFINER=`root`@`localhost` PROCEDURE `DeleteCard`(IN `aName` VARCHAR(255))
NO SQL
BEGIN
UPDATE Decks 
JOIN Amount ON amount.DeckName = decks.DeckName 
SET decks.DeckTotal = Decks.DeckTotal - Decks.DeckTotal
WHERE Amount.AmountName = aName;

MySQL said: Documentation

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 '' at line 7

Community
  • 1
  • 1
bloopiebloopie
  • 309
  • 1
  • 4
  • 11
  • Possible duplicate of [How to write a stored procedure using phpmyadmin and how to use it through php?](http://stackoverflow.com/questions/2846516/how-to-write-a-stored-procedure-using-phpmyadmin-and-how-to-use-it-through-php) – Matt Raines Apr 23 '16 at 11:10
  • You either need to change the delimiter so that the first `;` is not interpreted as the end of the procedure, or use the dedicated stored routine interface. – Matt Raines Apr 23 '16 at 11:12

1 Answers1

0

In the last delete query, you're missing @ before variable name:

DELETE  
FROM Cards 
WHERE CardName = aName; -- should be @aName

Also, remove the 

DEFINER=`root`@`localhost`

from your procedure, if you have any intentions to access your database remotely ever.

Shaharyar
  • 12,254
  • 4
  • 46
  • 66
  • Hey again Shaharyar, I added the @ symbol but it is still throwing the same error at the same pice of the code. Line 7, that is the line that reads "WHERE Amount.AmountName = @aName;" – bloopiebloopie Apr 23 '16 at 07:38
  • Are you running this complete script as an statement? If yes, then please do use phpmyadmin GUI to create it. Besides, all queries look fine except `lowercase` and `uppercase` table names somewhere. – Shaharyar Apr 23 '16 at 07:42
  • I used phpmyadmin to create the statement, then I re uploaded the database, like a tool. i copied back in the statement to the new database and here I am... Editing the names didn't change anything. – bloopiebloopie Apr 23 '16 at 07:46