469,950 Members | 1,883 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Paging using Mysql Stored Proc

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
1 9673
pbmods
5,821 Expert 4TB
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.

Similar topics

4 posts views Thread by david | last post: by
4 posts views Thread by Happy | last post: by
2 posts views Thread by Ilyas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.