Code sharing of paging stored procedures for SQL 2000

Copy codeThe Code is as follows :----------------------------------------------------
-- Paging stored procedures for SQL 2000
-- Time:
----------------------------------------------------
Alter procedure [dbo]. [uoSp_RecordPager]
-- The name of the table to be displayed by page. Multiple tables can be connected, but aliases cannot be used.
-- Example: uo_Article left join uo_ArticleClass ON uo_Article.AClassID = uo_ArticleClass.ID
@ Table_info varchar (100 ),

-- Used to locate the record's primary key (unique key) field, which can be multiple fields separated by commas
@ Field_id nvarchar (1000 ),

-- Page number to be displayed
@ CurrentPage int = 1,

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

-- List of fields to be displayed separated by commas (,). If not specified, it is *. For multi-table join query, if a field with the same name exists, the specified field must be displayed.
-- Example: uo_Article. *, uo_ArticleClass.ClassName
@ Field_info nvarchar (1000 ),

-- A List Of sorting fields separated by commas (,). You can specify DESC/ASC after a field to specify the sorting order.
@ Field_Order nvarchar (1000) = '',

-- Query Condition
@ Otherwhere nvarchar (1000) = '',
@ RecordCount int OUTPUT, -- total number of rows
@ PageCount int OUTPUT, -- total number of pages
@ SQL STR nvarchar (2000) output
AS
SET NOCOUNT ON

-- Paging field check
If isnull (@ field_id, n'') =''
BEGIN
RAISERROR (n' primary key (or unique key) required for paging processing)
RETURN
END

-- Other parameter checks and specifications
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 NULL, the total number of pages is calculated (this design can only calculate the total number of pages for the first time. When called later, the total number of pages is returned to the stored procedure to avoid re-calculation of the total number of pages, if you do not want to calculate the total number of pages, you can assign a value to @ 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

-- Display directly on the first page
IF @ CurrentPage = 1
BEGIN
SET @ SQLSTR = n' SELECT top' + str (@ PageSize) + n'' + @ Field_info + N' FROM '+ @ Table_info + N' + @ otherwhere + N' + @ Field_Order
-- PRINT @ SQLSTR
EXEC (@ SQLSTR)
END
ELSE
BEGIN
----------------------------------------------------
-- Get the 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

-- Construct an SQL statement
SET @ SQLSTR = n' SELECT * FROM ('
+ N' select top '+ STR (@ PageSize * @ CurrentPage) + n'' + @ Field_info + N' FROM '+ @ Table_info + N' + @ otherwhere + N' + @ Field_Order
+ 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_Order
+ N ')'
+ N'' + @ Field_Order
EXEC (@ SQLSTR)
----------------------------------------------------
END