Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 13th, 2003, 07:21 AM
Authorized User
 
Join Date: Oct 2003
Location: Delhi, Delhi, India.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to SubodhKumar Send a message via Yahoo to SubodhKumar
Default Paging of Sql Query Result.

Hi All,
I am using Microsoft SQL Server 2000.
Actuall I want to show the result page wise. To do this ,
I want to implement a stored procedure in which I want to pass two argument s “sql_Query” and “page_number”.

Suppose we got 75 records found as result of sql_Query. And I have fixed page size 20,
Now,
Result 1 should contains 20
Result 2 should contains 20
Result3 should contains 20
Result 4 should contains 15

When I will execute procName “sql_Query”,2
    It must show the Result2
When I will execute procName “sql_Query”,4
    It must show the Result4

I want to use only Sql Server 2000, Can Anyone Help Me to implement this stored procedure or give me the code for such type of stored procedure.




Enjoy!
Subodh Kumar
Phoneytunes.com
__________________
<font size=\"2\"><font color=\"blue\"><b>Enjoy!
Subodh Kumar
Phoneytunes.com
</b></font id=\"blue\"></font id=\"size2\">
Reply With Quote
  #2 (permalink)  
Old December 13th, 2003, 10:39 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Execute your query and store the results to a temp table. If you are passing a query into the sproc (so you can use a standard paging sproc) you'll need to either figure out what the columns are so you can create the temp table before inserting the records, or you can use the "SELECT ... INTO #temptable FROM" syntax. This might be easier to do because you don't need to worry about the columns, but you'll need to insert the "INTO #temptable" bit into the statement. This shouldn't be too hard (i don't know the syntax off hand). The end result is this:

SELECT <columns> INTO #pagingTable FROM <rest of query>

Then you add an identity column to the temp table...

ALTER TABLE #pagingTable ADD pagingRows INT IDENTITY

Now you have a column with numbers in sequence based on your original query order and you can select a chunk of the rows based on your page number and size:

DECLARE @pageSize INT
SET @pageSize = 20
SELECT * FROM #pagingTable
WHERE pagingRows >((@page_number-1)*@pageSize)
AND pagingRows <= ((@page_number-1)*@pageSize)+@pageSize

One note regarding this: Your source query must not contain an identity column because of the way "INTO ..." works. That operation creates the same column types (identities included) as the source query table(s) so you can't add another identity column.

I'm sure there is a better way to do this (Jeff?) that also doesn't result in the identity column problem.

Peter
------------------------------------------------------
Work smarter, not harder.
Reply With Quote
  #3 (permalink)  
Old December 13th, 2003, 12:35 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:
I'm sure there is a better way to do this (Jeff?) that also doesn't result in the identity column problem.
This is one of those problems that I don't think has a good solution, I'm afraid.

We discussed this a bit in http://p2p.wrox.com/topic.asp?TOPIC_ID=7041.

The problem with using a temp table is that it disappears when the connection is closed. This makes it especially problematic in a web page where you have to find some way to maintain the connection state across pages, which doesn't always work too well.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #4 (permalink)  
Old December 13th, 2003, 06:37 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Why do you need to maintain the connection state? You are passing the page number you want into the SPROC. When the page is drawn, you provide paging links. Each different server hit recalls the SPROC with the new page number.

No connection state is ever maintained for a web application. At least no web application I've ever written.

Peter
------------------------------------------------------
Work smarter, not harder.
Reply With Quote
  #5 (permalink)  
Old December 13th, 2003, 06:58 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

My bad. When I look at your post a bit closer, I now see that the stored procedure completely recreates the entire #temp table each time it is called. For some reason I thought you were creating the #temp table once then having the stored procedure page through it.

I didn't realize that the whole query was being executed for each page - I looked at it too quickly.

My comment about connection state was based on the faulty assumption that the #temp table was created once and then reused repeatedly, and that would not work in a web situation.

Sorry for misunderstanding.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Executing an SQL query and using it's result Andrew.Berry ASP.NET 2.0 Professional 5 April 14th, 2008 08:25 AM
check the result of sql query Abhinav_jain_mca ADO.NET 2 August 11th, 2004 11:58 AM
xml result set truncated in sql query analyzer xologist SQL Server 2000 2 March 22nd, 2004 05:23 PM
Setting a Variable = Result of a SQL Query PeteS VB Databases Basics 1 June 20th, 2003 08:14 AM



All times are GMT -4. The time now is 11:01 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.