Stored Procedure for creating prev-next links within detail page

[adpk77]

I am not very familiar with stored procedures. I am working on tutorial on how to create previous and next links within a detail page that navigates dynamically by displaying details of each record as contained within the mysql database.

In the tutorial, it had some sql code on creating the stored procedure but i feel it is not for use with mysql since the syntax is different.
Below is the code from the tutorial.
.......................................................
CREATE PROCEDURE spGetNextAndPreviousItems
@id int,
@prev int output,
@next int output
AS
SELECT top 1 @prev = blog_item_id
FROM blog_items
WHERE blog_item_id< @id
ORDER BY blog_item_id DESC
SELECT top 1 @next = blog_item_id
FROM blog_items
WHERE blog_item_id> @id
ORDER BY blog_item_id
..........................................................

Since the above code was giving errors, i referred to the mysql book and tried modyfying the code to match the mysql syntax but it still giving errors.
Below is the code.
..........................................................
CREATE PROCEDURE spGetNextAndPreviousItems
(@id int,
OUT @prev int,
OUT @next int)
BEGIN
SELECT blog_item_id
INTO @prev
FROM blog_items
WHERE blog_item_id< @id
ORDER BY blog_item_id DESC;
SELECT blog_item_id
INTO @next
FROM blog_items
WHERE blog_item_id> @id
ORDER BY blog_item_id;
END;
.....................................................
Where am i going wrong?
below are the errors.
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 '@id int,
OUT @prev int,
OUT @next int)
BEGIN
SELECT blog_item_id
INTO @prev
FROM' at line 2


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

 

[johnwm]

I believe you shouldn't use the semicolon ; inside the procedure as it is interpreted as a delimiter. Change the delimiter before storing your procedure and change back afterwards. See

http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html

about halfway down

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[r937]

the tutorial you were copying used TOP 1, so in your procedure, you would use LIMIT 1

however, the two queries inside your stored procedure could be simplified like this:

select blog_item_id
     , ( select min(blog_item_id)
           from blog_items
          where blog_item_id > @id ) as @next 
     , ( select max(blog_item_id)
           from blog_items
          where blog_item_id < @id ) as @prev
  from blog_items
 where blog_item_id = @id

r937.com | rudy.ca