2

Using Ver 14.12 Distrib 5.0.45 (I know it's old),

file:storedprocedure.sql contains:

DELIMITER //
CREATE PROCEDURE loadDashboard 
(
    IN limitStr int(11)
)
BEGIN
    SELECT table123.ID
    FROM table123
    ORDER BY date_lastmodified LIMIT limitStr;
END //
DELIMITER ;

I've tried both executing this command-line with:

mysql -u root -p -h localhost DB < storedprocedure.sql

and from within

mysql -u root -p -h localhost DB

mysql> *copied the code in from storedprocedure.sql

The error I get 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 'limitStr

However, another question on StackOverflow uses this exact syntax and it worked for everyone else?

Community
  • 1
  • 1
Kellen Stuart
  • 7,775
  • 7
  • 59
  • 82
  • `SELECT table_id` ***`FROM (what?)`*** . . . Please edit your post to include the actual content. – Mike Robinson Jul 08 '16 at 18:41
  • In order to address a question like this, we need to see *e-x-a-c-t-l-y* what you typed in, and *e-x-a-c-t-l-y* what the response was. The usual way that I do this is to start the `mysql` command, then, in this case: `source storedprocedure.sql;`. *(IIRC ...)* But your use of `<` should work too. – Mike Robinson Jul 08 '16 at 18:43
  • @MikeRobinson Ok. It is fully updated. – Kellen Stuart Jul 08 '16 at 18:44
  • I'm finding that this is only a problem when I add the parameter after the keyword `LIMIT`? This statement works fine if I replace `limitStr` with a number. – Kellen Stuart Jul 10 '16 at 16:22

1 Answers1

6

You were using a reserved word table. Try the following. Works fine as tested. If you need to use an if-y word like a Reserved Word, then surround it with back-ticks. This includes words for column names and tables that include a space or a hyphen.

schema:

drop table if exists mytable123;
create table mytable123
(
    id int auto_increment primary key,
    date_lastmodified datetime not null
);

insert mytable123(date_lastmodified) values ('2006-07-23'),('2006-07-27 13:10:09');

Stored proc:

drop procedure if exists loadDashboard;
DELIMITER //
CREATE PROCEDURE loadDashboard 
(
    IN limitStr int(11)
)
BEGIN
    DECLARE theLimit int;   -- to maintain compatibility (see comment from user wchiquito)

    set theLimit=limitStr;

    SELECT  ID
    FROM mytable123
    ORDER BY date_lastmodified 
    LIMIT theLimit; -- use the local variable for this
END //

DELIMITER ;

test:

call loadDashboard(1);
call loadDashboard(17);

MySQL Reserved Words ... the ones with an (R).

Drew
  • 24,851
  • 10
  • 43
  • 78
  • +1 for the detailed answer. You are going to be mad at me :| My table is not actually called `table` in the database. I was trying to use an example and I ended up using a keyword. Sorry about that. I'll keep that in mind next time I'm formulating one of these questions. I edited my question. – Kellen Stuart Jul 08 '16 at 19:46
  • mad at a +1, *never* :p – Drew Jul 08 '16 at 19:47
  • 1
    @Drew: Remember, `Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.` [13.2.9 SELECT Syntax](https://dev.mysql.com/doc/refman/5.5/en/select.html), in 5.0.45 does not support. For a workaround, see [Bug #11918 SP does not accept variables in LIMIT clause](https://bugs.mysql.com/bug.php?id=11918). – wchiquito Jul 09 '16 at 17:06
  • @wchiquito I found some cool question and thought of you a week ago. I need to spook it up. Some guy wanting a SIGNAL and I had poached some of your stuff a few weeks ago (pretty sure I left a comment). – Drew Jul 09 '16 at 17:07
  • @Drew: Interesting version of `TRANSACTION` and `SIGNAL` use in that answer. – wchiquito Jul 09 '16 at 17:26
  • @wchiquito That is absolutely the answer to this question. It looks like I am not able to directly pass a parameter to the limit string. I found the answer in this post http://stackoverflow.com/questions/2875238/passing-limit-as-parameters-to-mysql-sproc . I'll just dynamically create the limit string in php – Kellen Stuart Jul 10 '16 at 16:26
  • you can do it with a prepared statement (a concat) in the stored proc. I mean, not that you would want to, but you could. For tricky stuff. Like [the in clause](http://stackoverflow.com/a/37996572) – Drew Jul 10 '16 at 16:30