473,574 Members | 3,149 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Paging using Mysql Stored Proc

15 New Member
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 10621
pbmods
5,821 Recognized Expert Expert
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

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

Similar topics

4
1722
by: david | last post by:
Hi I have written code in ASP for paging records from the database (SQL Server 2000). The real problem I have around 10,000 records and it tries to fetch all the records everytime (I'm saying because its take a lot time to display it). Even though, it displays all the data correctly and you can also navigate through links. Is it possible...
4
1831
by: Happy | last post by:
hi I got a datagrid with Paging enabled which is getting populated thru a stored proc. I've given the code in the event grid.CurrentPageIndex = e.NewPageIndex grid.VirtualItemCount = //COUNT FROM STORED PROC grid.Datasource =a; grid.DataBind();
0
3375
by: anonieko | last post by:
This approach I found very efficient and FAST when compared to the rowcount, or Subquery Approaches. This is before the advent of a ranking function from DB such as ROW_NUMBER() in SQL Server 2005 and the likes of it. So This one works with SQL2000 What do you think?
1
10105
by: Radhakrishnans | last post by:
hai friends i am new to java and mysql.i have created stored procedures in mysql.Now i want to call that stored procedures in java.All works fine.but it show the following error. unreported exception java.lang.InstantiationException; must be caught or declared to be thrown Class.forName ("com.mysql.jdbc.Driver").newInstance(); I need...
1
4140
by: rbg | last post by:
I am using derived tables to Page data on the SQL Server side. I used this link as my mentor for doing paging on the SQL Serverhttp://msdn2.microsoft.com/en-us/library/ms979197.aspx I wanted to use USER PAGING, thus I used the following code: CREATE PROCEDURE UserPaging ( @currentPage int = 1, @pageSize int =1000 ) AS
8
2792
by: rbg | last post by:
I did use query plans to find out more. ( Please see the thread BELOW) I have a question on this, if someone can help me with that it will be great. In my SQL query that selects data from table, I have a where clause which states : where PermitID like @WorkType order by WorkStart DESC
2
2654
by: rbg | last post by:
You are right, I did not include the exact query since it has a whole of joins and many where clauses in it. I did not want to make the post very hard to read, hence I simplified it. In the Stored proc I am using a String variable @SQLString varchar(2000) to hold the entire select statement, and then executing that SQL using EXEC...
0
1554
by: av~ | last post by:
Hi All , I am trying to call a db2 stored proc with cursor open from my java code but it always returns me null results though it executes fine when I call the procedure from the command line Java code.................... CallableStatement stmt3 = EyeHibernateApp.getHibernateSession().connection().prepareCall("{call answers_select_id( ?...
2
1339
by: Ilyas | last post by:
Hi all I need to implmenet paging across different tables. The tables all have a different name eg Data01, data02 data03 etc, however they are columns which are common to each table, but each table also has some unique columns My questions is that I want to display data from any one of these tables - I wont know which one until runtime,...
0
8052
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8234
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7815
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5620
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5300
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3740
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3749
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2243
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1339
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.