By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,180 Members | 1,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,180 IT Pros & Developers. It's quick & easy.

Paging using Mysql Stored Proc

P: 15
Hi, I am new to using mysql and there isn't any tutorials online on that shows how to create mysql stored procedure for paging purposes. Thus, I read tutorials on creating stored proc that were written for use with SQL Server. I just need to be shown how to convert the syntax to one that is compatible with MySQL. The following is the stored proc written for SQL Server, please show me how to convert to a MySQL compatible syntax. Thank you in advance for your help.

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE uspPaging
  2. @nStartValue INT,
  3. @nEndValue INT
  4. AS
  5. SET NOCOUNT ON
  6.  
  7. DECLARE @tblTempData TABLE
  8. (
  9. NumID INT IDENTITY,
  10. ResID INT,
  11. ResType VARCHAR(50),
  12. ResDoc Blob
  13.  
  14. )
  15.  
  16. INSERT INTO @tblTempData
  17. (
  18. NumID,
  19. ResID,
  20. ResType,
  21. ResDoc,
  22. Firstn,
  23. Lastn
  24. )
  25. SELECT
  26. ResumeID,
  27. DocDate,
  28. Resumes,
  29. DocType,
  30. FirstName,
  31. LastName,
  32. DocDate
  33. FROM ResumeDB
  34. where Make = '" & LBoxProfessions.Items(i).Text & "'"
  35.  
  36. SELECT EmployeeID,
  37. ResID,
  38. ResType,
  39. ResDoc,
  40. Firstn,
  41. Lastn
  42. FROM @tblTempData
  43. WHERE nID BETWEEN @nStartValue AND @nEndValue
  44. ORDER BY
  45. nID ASC
Aug 3 '07 #1
Share this Question
Share on Google+
1 Reply


pbmods
Expert 5K+
P: 5,821
Heya, WebNewbie.

Hate to burst your bubble, but that's why MySQL has a LIMIT clause:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.         `ResumeID`,
  3.         `DocDate`,
  4.         `Resumes`,
  5.         `DocType`,
  6.         `FirstName`,
  7.         `LastName`,
  8.         `DocDate`
  9.     FROM
  10.         `ResumeDB`
  11.     WHERE
  12.         `Make` = '...'
  13.     LIMIT
  14.         {START},{MAX}
  15.  
Where START is the number of rows you want to skip, and MAX is the maximum number of rows.

For example, if each page had 20 results, you would use these LIMIT clauses:
For page 1: LIMIT 0,20
For page 2: LIMIT 20,20
For page 3: LIMIT 40,20

And so on.
Aug 5 '07 #2

Post your reply

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