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\">

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.

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

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.

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