CODEWITHSUNDEEP
mysqlstored-procedures
Charles Harmon
Charles Harmon

Reputation: 380

MySQL stored procedure 1064 Error

For the life of me, I can't figure out why I'm getting this error:

[Err] 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 'NULL' at line 1

I'm calling a simple stored procedure with some variables passed in. What you don't see here is the setting of the delimiter to //, but that's done as well.

Thanks much!

SET @dest_database                   = 'my_db';
SET @table_category_management_pages = 'category_management_pages';


 CREATE PROCEDURE category_management_pages(
    IN in_name varchar(255),
    IN in_display varchar(255),
    IN in_notes varchar(255),
    IN in_order INT,
    IN in_title varchar(255),
    IN in_access_name varchar(255)
  )
  BEGIN
    IF NOT EXISTS(
      SELECT * FROM information_schema.COLUMNS
      WHERE
      TABLE_SCHEMA = @dest_database AND
      TABLE_NAME   = @table_category_management_pages AND
      COLUMN_NAME  = 'key_hash'
    ) THEN
      # Add UNIQUE index on key_hash
      SET @myPrep = CONCAT('ALTER TABLE `', @dest_database, '`.`', @table_category_management_pages, '` ADD COLUMN `key_hash` varchar(255) NULL, ADD UNIQUE INDEX (`key_hash`);');
      prepare stmt from @myPrep;
      execute stmt;
    END IF;

    # Update key_hash to latest hash
    SET @myPrep = CONCAT('UPDATE `', @dest_database, '`.`', @table_category_management_pages, '` 
      SET `key_hash` = md5(`name`)');
     prepare stmt from @myPrep;
     execute stmt;

END//

Upvotes: 0

Views: 1155

Answers (1)

Charles Harmon
Charles Harmon

Reputation: 380

I'm answering my own question because I found out why the prepared statement wasn't working and it is something really simple dumb, but may affect others in the future. At our company, we have a habit of lining up all the equal signs when declaring variables (see edits above). Well, it seems like MySQL doesn't like whitespace in this manner, so the global variables weren't being passed in the prepared statement correctly. By changing the declaration to this:

/* Globals */
SET @dest_database = 'my_db';
SET @table_category_management_pages = 'category_management_pages';

...worked out fine. Go figure. Both suggestion in the comments were good, but in the end it was something due to whitespace. Very frustrating, but I hope this helps someone in the future.

Upvotes: 0

Related Questions