Split-page stored procedure Code sharing for SQL 2000 _mssql

Copy Code code as follows:

----------------------------------------------------
--Paging stored procedures for SQL 2000
--time:2008-9-25
----------------------------------------------------
ALTER PROCEDURE [dbo]. [Uosp_recordpager]
--The name of the table to be paged, multiple tables to check, but not aliases.
Example: Uo_article left JOIN uo_articleclass on uo_article.aclassid=uo_articleclass.id
@Table_info varchar (100),

--The primary key (unique key) field used to locate records, which can be multiple fields separated by commas
@field_id nvarchar (1000),

--the page number to display
@CurrentPage Int=1,

--The size of each page (number of records)
@PageSize int=10,

--a comma-delimited list of fields to display, or * if not specified. However, if you have the same name section in multiple table joins, you must display the fields you want to search for.
--Example: Uo_article.*,uo_articleclass.classname
@Field_info nvarchar (1000),

--a comma-delimited list of sorted fields that specify DESC/ASC to specify the sort order after the field
@Field_Order nvarchar (1000) = ',

--Query criteria
@otherwhere nvarchar (1000) = ',
@RecordCount int OUTPUT,--Total number of rows
@PageCount int OUTPUT,--Total pages
@SQLSTR nvarchar (watts) output
As
SET NOCOUNT on

--page-field check
IF ISNULL (@field_id, N ') = '
BEGIN
RAISERROR (N ' paging requires a primary key (or a unique key) ', 1,16
Return
End

--Inspection and specification of other parameters
IF ISNULL (@PageSize, 0) <1 SET @PageSize =10
IF ISNULL (@Field_info, N ') =n ' SET @Field_info =n ' * '
IF ISNULL (@Field_Order, N ') =n '
SET @Field_Order =n '
ELSE
SET @Field_Order =n ' ORDER by ' +ltrim (@Field_Order)

IF ISNULL (@otherwhere, N ') =n '
SET @otherwhere =n '
ELSE
SET @otherwhere =n ' WHERE (' + @otherwhere +n ') '

--Calculate @recordcount
declare @sqlt nvarchar (1000)
Set @sqlt = ' SELECT @RecordCount = COUNT (' + @Field_id + ') from ' + @Table_Info + @otherwhere
EXEC sp_executesql @sqlt, N ' @RecordCount int output ', @RecordCount output

--If @pagecount is a null value, the total number of pages is computed (so that the design can be calculated only for the first total number of pages, and then when called, the total number of pages will be passed back to the stored procedure to avoid the calculation of the overall number of pages, you can assign a value to the @pagecount
IF @PageCount is NULL
BEGIN
DECLARE @sql nvarchar (4000)
SET @sql =n ' SELECT @PageCount =count (*) '
+n ' from ' + @Table_info
+n ' + @otherwhere
EXEC sp_executesql @sql, N ' @PageCount int output ', @PageCount output
SET @PageCount = (@PageCount + @PageSize-1)/@PageSize
End

IF ISNULL (@CurrentPage, 0) <1 SET @CurrentPage =1
IF @CurrentPage > @PageCount and @PageCount >0 SET @CurrentPage = @PageCount

--The first page shows directly
IF @CurrentPage = 1
BEGIN
SET @SQLSTR = N ' SELECT top ' + str (@PageSize) +n ' + @Field_info +n ' from ' + @Table_info +n ' + @otherwhere +n ' + @Field_Orde R
--print @SQLSTR
EXEC (@SQLSTR)
End
ELSE
BEGIN
----------------------------------------------------
--Gets the table name of the first table
DECLARE @FirstTableName varchar (20)
SET @FirstTableName = @Table_info
IF CHARINDEX (n ', ', @FirstTableName) >0 OR CHARINDEX (n '. ', @FirstTableName) > 0 OR CHARINDEX (n ', @FirstTableName) > 0
BEGIN
While CHARINDEX (N ', ', @FirstTableName) > 0
SELECT @FirstTableName =left (@FirstTableName, CHARINDEX (N ', ', @FirstTableName)-1)
While CHARINDEX (N '. ', @FirstTableName) > 0
SELECT @FirstTableName =left (@FirstTableName, CHARINDEX (N '. ', @FirstTableName)-1)
While CHARINDEX (N ', @FirstTableName) > 0
SELECT @FirstTableName =left (@FirstTableName, CHARINDEX (N ', @FirstTableName)-1)
End

--Constructing SQL statements
SET @SQLSTR = N ' SELECT * FROM ('
+ n ' SELECT top ' +str (@PageSize * @CurrentPage) +n ' + @Field_info + n ' from ' + @Table_info +n ' + @otherwhere +n ' + @Field_Or Der
+ N ') ' + @FirstTableName +n ' WHERE ' + @field_id +n ' not in ('
+ n ' SELECT top ' +str (@PageSize * (@CurrentPage-1)) +n "+ @field_id + n ' from ' + @Table_info +n ' + @otherwhere +n ' + @Field_O Rder
+ N ') '
+ N ' + @Field_Order
EXEC (@SQLSTR)
----------------------------------------------------
End