This is the code for Paging in SQL Server. ONLY uses the 2 lines of Select Statement!!!!
code: Can't Copy and Paste this? Click here for a copy-and-paste friendly version of this code!
Terms of Agreement:
By using this code, you agree to the following terms...
You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
You may link to this code from another website, but ONLY if it is not wrapped in a frame.
You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
--**************************************
-- Name: SQL Server Paging
-- Description:This is the code for Paging in SQL Server. ONLY uses the 2 lines of Select Statement!!!!
-- By: Darpan Gogia
--
-- Inputs:You can execute like:-
Exec ReturnRows 'Select * from <<TableName>>',<<Page No.>>,<<Records Per Page>>,<<IDENTITY Column Name>>,<<Column to Sort On>>
Exec MMReturnRows 'Select * from s',2,5,'EmployeeID','FirstName'
--
-- Returns:No. It Just executes the Select Statement
--
-- Side Effects:No
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=633&lngWId=5--for details.--**************************************
CREATE PROCEDURE ReturnRows
(
@SQL nvarchar(4000),
@Page int,
@RecsPerPage int,
@ID varchar(255),
@Sort varchar(255)
)
AS
DECLARE @Str nvarchar(4000)
SET @Str='Select top '+CAST(@RecsPerPage AS varchar(20))+' * from ('+@SQL+') T where T.'+@ID+' not in
(select top '+CAST((@RecsPerPage*(@Page-1)) AS varchar(20))+' '+@ID+' from ('+@SQL+') T9 order by '+@Sort+') order by '+@Sort
Print @Str
EXEC sp_ExecuteSql @Str
GO
Report Bad Submission
Your Vote
Other User Comments
6/2/2003 8:02:08 AM:
Good job, this is just what I was looking for. Thanks (If this comment was disrespectful, please report it.)
6/11/2003 8:14:05 AM:
Hi All
I modified this to add the following lines :
DECLARE @Str2 nvarchar(4000) SET @Str2 = 'SELECT COUNT(*) AS Recs FROM (' + @SQL + ') T10' EXEC sp_ExecuteSql @Str2
This will now return two recordsets, the first contains the total records found, this will assist in building up a paging mechanism for next/previous or jump to page. (If this comment was disrespectful, please report it.)
8/7/2003 3:49:18 PM:
I am new to SPROCS, can someone show me what the proper execution of this code is with the modifications looks like. I can't get it to return both recordsets at the same time. (If this comment was disrespectful, please report it.)
9/8/2003 10:59:23 PM:
Darpan, youve just saved my job. thanks for the code. (If this comment was disrespectful, please report it.)
9/23/2003 11:05:28 PM:
ff (If this comment was disrespectful, please report it.)
9/23/2003 11:07:22 PM:
thanks for code here some changes/improvements? to count section. _______________________________________ DECLARE @Str2 nvarchar(4000) declare @my_count int declare @param_def nvarchar(25) set @Str2 = ' SELECT @count=COUNT(*) FROM (' + @SQL + ') T10' set @param_def=N'@count int output' EXEC sp_ExecuteSql @Str2,@param_def,@count=@my_count output print @my_count ______________________________________ now @my_count can be assigned to the output parameter of the sp.
(If this comment was disrespectful, please report it.)
1/23/2005 11:50:29 PM:
Paging queries has some issues with performance , if you increase the number of pages like 1000 it will go slow and will return the results in minutes. (If this comment was disrespectful, please report it.)
4/20/2005 2:41:28 PM:
Another solution in http://www.microsoft.com/spanish/msdn/comunidad/mtj.net/voices/art167.asp
The subquery may be anything.
This example returns the rows of the page 2 (20 rows by page). And is very easy know the total rows.
SELECT Identity(int, 1, 1) AS row_number, temp_table_paging.* INTO #TEMP_PAGING_TABLE FROM ( SELECT * FROM SALES ) as temp_table_paging
SELECT * FROM #TEMP_PAGING_TABLE WHERE row_number >= 20 AND row_number < 39
SELECT max(row_number)as cantidad FROM #TEMP_PAGING_TABLE
DROP TABLE #TEMP_PAGING_TABLE (If this comment was disrespectful, please report it.)
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular code, please
click here instead.)
By 9 Users
6/2/2003 8:02:08 AM: 
