/*
@ CurrentPage: displays the page,
@ PageSize: the number of rows displayed on each page,
@ Field_info: the field to be displayed can be *,
@ Table_info: the table or view to be queried,
@ Field_id: primary key or unique field,
@ Field_Order: sorting field,
@ Otherwhere is a condition without "WHERE ",
@ RecordCount: total number of rows, OUTPUT
@ PageCount: total page number, OUTPUT
@ SQLSTR if an error occurs, you can use this parameter to output an SQL statement.
*/
Alter procedure [dbo]. [uoSp_RecordPager]
@ CurrentPage int = 1,
@ PageSize int = 10,
@ Field_Info varchar (500 ),
@ Table_info varchar (100 ),
@ Field_id varchar (20 ),
@ Field_Order varchar (100 ),
@ Otherwhere varchar (8000 ),
@ RecordCount int output,
@ PageCount int output,
@ SQL STR varchar (8000) output
AS
Begin
DECLARE @ MinPage int, @ MaxPage int
Declare @ SQL varchar (8000)
Declare @ sqlt nvarchar (4000)
Set @ sqlt = 'SELECT @ RecordCount = COUNT ('+ @ Field_id +') FROM '+ @ Table_Info
IF @ otherwhere! =''
Set @ sqlt = @ sqlt + 'where' + @ otherwhere
Exec sp_executesql @ sqlt, n' @ RecordCount int output', @ RecordCount output
-- How to put the exec execution result into a variable. If it is a string, use N. The variable after N must have the same name as the variable in @ sqlt.
IF @ PageSize <= 0
Begin
Set @ PageSize = 10
End
-- Else if @ PageSize> @ RecordCount
-- Begin
-- Set @ pageSize = @ RecordCount
-- End
Set @ pagecount = @ RecordCount/@ PageSize
If (@ recordcount % @ pagesize )! = 0) -- add one page if no cleaner exists
Begin
Set @ PageCount = @ RecordCount/@ PageSize
Set @ PageCount = @ pagecount + 1
End
Else
Begin
Set @ pagecount = @ recordcount/@ PageSize
End
IF @ CurrentPage <= 0
Begin
Set @ CurrentPage = 1
End
Else if @ CurrentPage> @ pagecount
Begin
Set @ currentpage = @ pagecount -- if the input page number is greater than the total page number, it indicates the last page.
End
SET @ MinPage = (@ CurrentPage-1) * @ PageSize + 1
SET @ MaxPage = @ MinPage + @ PageSize-1
BEGIN
If @ Field_Info like''
Set @ field_Info = '*'
IF @ otherwhere like''
Set @ SQL = 'SELECT top '+ str (@ PageSize) +' * from
(SELECT '+ @ Field_Info +', row_number () over (order by '+ @ Field_Order +') as rownumber
From '+ @ Table_info +'
) As TMP_TABLE where (rownumber between '+ convert (varchar (10), @ minpage) + 'and' + convert (varchar (10), @ maxpage) + ')'
ELSE
Set @ SQL = 'SELECT top '+ str (@ PageSize) +' * from
(SELECT '+ @ Field_Info +', row_number () over (order by '+ @ Field_Order +') as rownumber
From '+ @ Table_info +' where 1 = 1 and '+ @ otherwhere +'
) As TMP_TABLE where (rownumber between '+ convert (varchar (10), @ minpage) + 'and' + convert (varchar (10), @ maxpage) + ') and '+ @ otherwhere
EXEC (@ SQL)
SET @ SQLSTR = @ SQL
END
End |
