SQL server2000 A lot of paging, efficiency when you reach level 100,000, some of the difficulties, here for the time being no example out
SQL server2005 more than the row_number is also a great progress, the details are as follows
Copy Code code as follows:
SQL Server2005 Common Paging stored procedures
CREATE PROCEDURE [dbo]. [Common_getpagedlist]
(
@TableName nvarchar (100),--table name
@ColumnNames nvarchar (1000) = ' * ',--field name collection (All fields are *, other comma separated)
@OrderClause nvarchar (1000),--sort clauses (without ORDER BY)
@WhereClause nvarchar (1000) =n ' 1=1 '--conditional clause (where not included)
@PageSize int = 0,--Number of records per page (0 for all)
@PageIndex int = 1,--page index (starting from 1)
@TotalRecord int output--Returns the total number of records
)
As
BEGIN
if (@ColumnNames is null or @ColumnNames = ') Set @ColumnNames = ' * '
if (@WhereClause is null or @WhereClause = ') Set @WhereClause = ' 1=1 '
if (@OrderClause is null or @OrderClause = ') Set @OrderClause = ' Id desc '
--processing start and end points
Declare @StartRecord int;
Declare @EndRecord int;
Declare @TotalCountSql nvarchar (1200);
Declare @SqlString nvarchar (4000);
--Statistical records
if (@TotalRecord is null OR @TotalRecord >=0)
Begin
SET @TotalCountSql = N ' Select @TotalRecord = count (*) from ' + @TableName + ' where ' + @WhereClause;
--select @TotalCountSql
EXEC sp_executesql @totalCountSql, N ' @TotalRecord int out ', @TotalRecord output;--returns the total number of records
End
If @PageSize >0
Begin
If @PageIndex <1 set @PageIndex =1
Set @StartRecord = (@PageIndex-1) * @PageSize + 1
Set @EndRecord = @StartRecord + @PageSize-1
Set @SqlString = N ' Select Row_number () over (order by ' + @OrderClause + ') as rowId, ' + @ColumnNames + ' from ' + @TableName + ' W Here ' + @WhereClause;
Set @SqlString = ' select * FROM (' + @SqlString + ') as T where rowId between ' + LTrim (str (@StartRecord)) + ' and ' + Ltri M (str (@EndRecord));
End
Else
Begin
Set @SqlString = ' SELECT ' + @ColumnNames + ' from ' + @TableName + ' where ' + @WhereClause + ' ORDER BY ' + @OrderClause
End
--select @SqlString
Exec (@SqlString)
End