Stored Procedure SELECT Statement Error

[kxyz]

Hello everyone,

I need help with a stored procedure or two.

My stored procedures are supposed to check if a certain record exists. If it does exist, then I select everything from that row, as well as a value for the exit status of the SP. If the record doesn't exist, it selects an error message as the exit status.

I've recreated the SP (while changing field names) and restructured the table, just to get straight to the point.



The table I'm working with looks something like this:

DROP TABLE IF EXISTS `test_table`;
CREATE TABLE `test_table` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(32));

As for the stored procedures, here is the first one:

DROP PROCEDURE IF EXISTS `test_sp2`;
DELIMITER //
CREATE PROCEDURE `test_sp2` (IN `p_id` INT)
BEGIN
    SELECT @num := COUNT(`id`) FROM `test` WHERE `id` = `p_id`;
    IF @num > 0 THEN
        SELECT * FROM `test` WHERE `id` = `p_id`;
        SELECT 0 AS `status`; -- 'Return value,' to be used by the app. 0 if SP executed as desired, otherwise it will contain an error message
    ELSE
        SELECT 'Record does not exist.' AS `status`;
    END IF;
END;
//
DELIMITER ;

Then here is the second SP, which I tried to rewrite with the IF EXISTS statement, but still no luck.

DELIMITER //
CREATE PROCEDURE `test_sp` (IN `p_id` INT)
BEGIN
    IF EXISTS(SELECT @num := COUNT(`id`) FROM `test` WHERE `id` = `p_id`) THEN
        SELECT * FROM `test` WHERE `id` = `p_id`;
        SELECT 0 AS `status`; -- 'Return value,' to be used by the app. 0 if SP executed as desired, otherwise it will contain an error message
    ELSE
        SELECT 'Record does not exist.' AS `status`;
    END IF;
END;
//
DELIMITER ;

The error I get (for either SP) is:

ERROR 1064 (42000): 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 '* FROM `test` WHERE `id` = `p_id`;

I've tried completely stripping down the SP, and it looks like the problem lies in the first SELECT statement.

What baffles me is that these were working just the other day, and now they're not.


I'm currently running MySQL 5.0.51a.


Any help or advice is greatly appreciated. If anyone needs more information out of me regarding this, I'd be glad to answer any questions.

[kxyz]

I found the solution. Using two or more tabs to indent (for any statement), will NOT work.

Indenting one tab will work or not indenting at all will work, but indenting with just two or more tabs is a syntax error.

Does not work (two tabs):

DROP PROCEDURE IF EXISTS `test_sp2`;
DELIMITER //
CREATE PROCEDURE `test_sp2` (IN `p_id` INT)
BEGIN
    SELECT @num := COUNT(`id`) FROM `test_table` WHERE `id` = `p_id`;
    IF @num > 0 THEN
        SELECT * FROM `test_table` WHERE `id` = `p_id`;
        SELECT 0 AS `status`; -- 'Return value,' to be used by the app. 0 if SP executed as desired, otherwise it will contain an error message
    ELSE
        SELECT 'Record does not exist.' AS `status`;
    END IF;
END;
//
DELIMITER ;

Works (one tab):

DROP PROCEDURE IF EXISTS `test_sp2`;
DELIMITER //
CREATE PROCEDURE `test_sp2` (IN `p_id` INT)
BEGIN
    SELECT @num := COUNT(`id`) FROM `test_table` WHERE `id` = `p_id`;
    IF @num > 0 THEN
    SELECT * FROM `test_table` WHERE `id` = `p_id`;
    SELECT 0 AS `status`; -- 'Return value,' to be used by the app. 0 if SP executed as desired, otherwise it will contain an error message
    ELSE
    SELECT 'Record does not exist.' AS `status`;
    END IF;
END;
//
DELIMITER ;

After messing around with this SP for a bit, I see that using two or more tabs will not only trigger an error for the statements inside the IF ELSE block, but anywhere in the SP.

Does not work (three tabs):

DROP PROCEDURE IF EXISTS `test_sp2`;
DELIMITER //
CREATE PROCEDURE `test_sp2` (IN `p_id` INT)
BEGIN
            SELECT @num := COUNT(`id`) FROM `test_table` WHERE `id` = `p_id`;
    IF @num > 0 THEN
    SELECT * FROM `test_table` WHERE `id` = `p_id`;
    SELECT 0 AS `status`; -- 'Return value,' to be used by the app. 0 if SP executed as desired, otherwise it will contain an error message
    ELSE
    SELECT 'Record does not exist.' AS `status`;
    END IF;
END;
//
DELIMITER ;

However, you can use two or more tabs for indentation, but you MUST include at least one space.

Works (two tabs and one space):

DROP PROCEDURE IF EXISTS `test_sp2`;
DELIMITER //
CREATE PROCEDURE `test_sp2` (IN `p_id` INT)
BEGIN
    SELECT @num := COUNT(`id`) FROM `test_table` WHERE `id` = `p_id`;
    IF @num > 0 THEN
         SELECT * FROM `test_table` WHERE `id` = `p_id`;
         SELECT 0 AS `status`; -- 'Return value,' to be used by the app. 0 if SP executed as desired, otherwise it will contain an error message
    ELSE
         SELECT 'Record does not exist.' AS `status`;
    END IF;
END;
//
DELIMITER ;

Also, I see that I mislabeled the table that the stored procedures pulling data from. In my example the table the stored procedures should have been selecting data from should have been `test` instead of `test_table`. This wasn't actually part of the problem, I had just created the `test` table as an example.



I hope this will help someone else down the road.

[Atli]

Hi.

Thanks for sharing the solution!

I got to ask tho. What OS are you using and how are you executing the command? (Like say, via the CLI?)

I tested this myself on my Ubuntu system, using the standard CLI, and I found that it worked no matter how many tabs I used.
But, I did get some weird display problems in the CLI when I added 2 or more tabs.

I wrote the code in Kate and pasted it into the CLI.

This was my test code:

DELIMITER $$
DROP PROCEDURE IF EXISTS getMaxMemoryRange$$
CREATE PROCEDURE getMaxMemoryRange(IN bits INT)
BEGIN
    SELECT POW(2, bits) / POW(1024, 3) AS 'Gigabytes';
END;
$$
DELIMITER ;
 

Which worked just as expected.

But when I added a second tab before the SELECT, the CLI input changed to this (when I pasted it):

mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS getMaxMemoryRange$$
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE PROCEDURE getMaxMemoryRange(IN bits INT)
    -> BEGIN
    ->
amarok              dates.date_col      mysql
date_col            dates.qty           qty
dates               information_schema  test
    -> SELECT POW(2, bits) / POW(1024, 3) AS 'Gigabytes';
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)
 
mysql> DELIMITER ;
 

It looks wrong but it still appeared to work as expected.

I thought his was a problem with the Linux kernel. Normally, when typing a path, double clicking the tab while in the middle of writing a path will show you suggestions as to how that path could be completed.

This kind of looks like it may be related.