Create procedure sp_pagination -- paging Stored Procedure (applicable when millions of pages have primary keys)
(
@ Fieldlist varchar (500), -- display the Field List
@ Tablename varchar (50), -- query the table name
@ ID varchar (50), -- Id field
@ Currentpage int, -- current page
@ Pagesize int, -- how many records are displayed on each page
@ Likelist varchar (8000), -- List of judgment Conditions
@ Orderlist varchar (1000), -- List of sorting statements
@ Recordcount int output -- total number of returned records
)
As
Declare @ SQL nvarchar (4000) -- subject sentence
Declare @ recordtotal int -- returns the total number of records in the current query result.
Declare @ strtmp varchar (1000) -- Temporary Variable
Declare @ strorder varchar (1000) -- sort type
Declare @ order int -- 0 in descending order, 1 in ascending order, determined by @ orderlist
Set @ SQL = "select @ reccount = count ([" + @ ID + "]) from" + @ tablename + "" + @ likelist + "" -- SQL query statement
Execute sp_executesql @ SQL, n' @ reccount int output', @ reccount = @ recordcount output -- get the total number of records
If substring (ltrim (replace (@ orderlist, "order by", ""), @ ID, ""), 1, 4) = "DESC"
Begin
Set @ order = 0 -- descending order
End
Else
Begin
Set @ order = 1 -- Ascending
End
If @ order = 0
Begin
Set @ strtmp = "<(select Min"
Set @ strorder = "order by [" + @ ID + "] DESC"
End
Else
Begin
Set @ strtmp = "> (select Max"
Set @ strorder = "order by [" + @ ID + "] ASC"
End
Set @ SQL = "select top" + STR (@ pagesize) + "" + @ fieldlist + "from [" + @ tablename + "] Where [" + @ ID + "]" + @ strtmp + "([" + @ ID +" ]) from (select top "+ STR (@ currentpage-1) * @ pagesize) + "[" + @ ID + "] from [" + @ tablename + "]" + @ strorder + ") as tbltmp)" + @ strorder
Set @ likelist = Replace (@ likelist, "where ","")
If @ likelist! =''
Begin
Set @ SQL = "select top" + STR (@ pagesize) + "" + @ fieldlist + "from [" + @ tablename + "] Where [" + @ ID + "]" + @ strtmp + "([" + @ ID +" ]) from (select top "+ STR (@ currentpage-1) * @ pagesize) + "[" + @ ID + "] from [" + @ tablename + "] Where" + @ likelist + "" + @ strorder + ") as tbltmp) and "+ @ likelist +" "+ @ strorder
End
If @ currentpage = 1
Begin
Set @ strtmp = ""
If @ likelist! =''
Set @ strtmp = "where" + @ likelist
Set @ SQL = "select top" + STR (@ pagesize) + "" + @ fieldlist + "from [" + @ tablename + "]" + @ strtmp + "" + @ strorder
End
Exec (@ SQL)
Go