469,347 Members | 1,710 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,347 developers. It's quick & easy.

Stored Procedure SELECT Statement Error

2
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:
Expand|Select|Wrap|Line Numbers
  1. DROP TABLE IF EXISTS `test_table`;
  2. CREATE TABLE `test_table` (
  3. `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  4. `name` VARCHAR(32));

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

Expand|Select|Wrap|Line Numbers
  1. DROP PROCEDURE IF EXISTS `test_sp2`;
  2. DELIMITER //
  3. CREATE PROCEDURE `test_sp2` (IN `p_id` INT)
  4. BEGIN
  5.     SELECT @num := COUNT(`id`) FROM `test` WHERE `id` = `p_id`;
  6.     IF @num > 0 THEN
  7.         SELECT * FROM `test` WHERE `id` = `p_id`;
  8.         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
  9.     ELSE
  10.         SELECT 'Record does not exist.' AS `status`;
  11.     END IF;
  12. END;
  13. //
  14. DELIMITER ;




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

Expand|Select|Wrap|Line Numbers
  1. DELIMITER //
  2. CREATE PROCEDURE `test_sp` (IN `p_id` INT)
  3. BEGIN
  4.     IF EXISTS(SELECT @num := COUNT(`id`) FROM `test` WHERE `id` = `p_id`) THEN
  5.         SELECT * FROM `test` WHERE `id` = `p_id`;
  6.         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
  7.     ELSE
  8.         SELECT 'Record does not exist.' AS `status`;
  9.     END IF;
  10. END;
  11. //
  12. DELIMITER ;

The error I get (for either SP) is:

Expand|Select|Wrap|Line Numbers
  1. 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.
Sep 8 '08 #1
2 3203
kxyz
2
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):
Expand|Select|Wrap|Line Numbers
  1. DROP PROCEDURE IF EXISTS `test_sp2`;
  2. DELIMITER //
  3. CREATE PROCEDURE `test_sp2` (IN `p_id` INT)
  4. BEGIN
  5.     SELECT @num := COUNT(`id`) FROM `test_table` WHERE `id` = `p_id`;
  6.     IF @num > 0 THEN
  7.         SELECT * FROM `test_table` WHERE `id` = `p_id`;
  8.         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
  9.     ELSE
  10.         SELECT 'Record does not exist.' AS `status`;
  11.     END IF;
  12. END;
  13. //
  14. DELIMITER ;


Works (one tab):
Expand|Select|Wrap|Line Numbers
  1. DROP PROCEDURE IF EXISTS `test_sp2`;
  2. DELIMITER //
  3. CREATE PROCEDURE `test_sp2` (IN `p_id` INT)
  4. BEGIN
  5.     SELECT @num := COUNT(`id`) FROM `test_table` WHERE `id` = `p_id`;
  6.     IF @num > 0 THEN
  7.     SELECT * FROM `test_table` WHERE `id` = `p_id`;
  8.     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
  9.     ELSE
  10.     SELECT 'Record does not exist.' AS `status`;
  11.     END IF;
  12. END;
  13. //
  14. 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):
Expand|Select|Wrap|Line Numbers
  1. DROP PROCEDURE IF EXISTS `test_sp2`;
  2. DELIMITER //
  3. CREATE PROCEDURE `test_sp2` (IN `p_id` INT)
  4. BEGIN
  5.             SELECT @num := COUNT(`id`) FROM `test_table` WHERE `id` = `p_id`;
  6.     IF @num > 0 THEN
  7.     SELECT * FROM `test_table` WHERE `id` = `p_id`;
  8.     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
  9.     ELSE
  10.     SELECT 'Record does not exist.' AS `status`;
  11.     END IF;
  12. END;
  13. //
  14. 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):
Expand|Select|Wrap|Line Numbers
  1. DROP PROCEDURE IF EXISTS `test_sp2`;
  2. DELIMITER //
  3. CREATE PROCEDURE `test_sp2` (IN `p_id` INT)
  4. BEGIN
  5.     SELECT @num := COUNT(`id`) FROM `test_table` WHERE `id` = `p_id`;
  6.     IF @num > 0 THEN
  7.          SELECT * FROM `test_table` WHERE `id` = `p_id`;
  8.          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
  9.     ELSE
  10.          SELECT 'Record does not exist.' AS `status`;
  11.     END IF;
  12. END;
  13. //
  14. 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.
Sep 8 '08 #2
Atli
5,058 Expert 4TB
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:
Expand|Select|Wrap|Line Numbers
  1. DELIMITER $$
  2. DROP PROCEDURE IF EXISTS getMaxMemoryRange$$
  3. CREATE PROCEDURE getMaxMemoryRange(IN bits INT)
  4. BEGIN
  5.     SELECT POW(2, bits) / POW(1024, 3) AS 'Gigabytes';
  6. END;
  7. $$
  8. DELIMITER ;
  9.  
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):
Expand|Select|Wrap|Line Numbers
  1. mysql> DELIMITER $$
  2. mysql> DROP PROCEDURE IF EXISTS getMaxMemoryRange$$
  3. Query OK, 0 rows affected (0.00 sec)
  4.  
  5. mysql> CREATE PROCEDURE getMaxMemoryRange(IN bits INT)
  6.     -> BEGIN
  7.     ->
  8. amarok              dates.date_col      mysql
  9. date_col            dates.qty           qty
  10. dates               information_schema  test
  11.     -> SELECT POW(2, bits) / POW(1024, 3) AS 'Gigabytes';
  12.     -> END;
  13.     -> $$
  14. Query OK, 0 rows affected (0.00 sec)
  15.  
  16. mysql> DELIMITER ;
  17.  
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.
Sep 8 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Bill Kellaway | last post: by
3 posts views Thread by Jarrod Morrison | last post: by
8 posts views Thread by Thomasb | last post: by
5 posts views Thread by Timppa | last post: by
1 post views Thread by peaceburn | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.