SQL Server paging Stored Procedure

Create procedure pagination
@ Tblname varchar (255), -- table name
@ Strgetfields varchar (1000) = '*', -- the column to be returned
@ Fldname varchar (255) = '', -- Name of the sorted Field
@ Pagesize Int = 10, -- page size
@ Pageindex Int = 1, -- page number
@ Docount bit = 0, -- returns the total number of records. If the value is not 0, the system returns
@ Ordertype bit = 0, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ Strwhere varchar (1500) = ''-- Query condition (Note: Do not add where)
As
Declare @ strsql varchar (5000) -- subject sentence
Declare @ strtmp varchar (110) -- Temporary Variable
Declare @ strorder varchar (400) -- sort type
If @ docount! = 0
Begin
If @ strwhere! =''
Set @ strsql = 'select count (*) as total from ['+ @ tblname +'] where' + @ strwhere
Else
Set @ strsql = 'select count (*) as total from ['+ @ tblname +']'
End
-- The above Code indicates that if @ docount is not passed over 0, the total number of statistics will be executed. All the code below is 0 @ docount
Else
Begin
If @ ordertype! = 0
Begin
Set @ strtmp = '<(select min'
Set @ strorder = 'order by ['+ @ fldname +'] desc'
-- If @ ordertype is not 0, execute the descending order. This sentence is very important!
End
Else
Begin
Set @ strtmp = '> (select Max'
Set @ strorder = 'order by ['+ @ fldname +'] ASC'
End
If @ pageindex = 1
Begin
If @ strwhere! =''
Set @ strsql = 'select top '+ STR (@ pagesize) + ''+ @ strgetfields + 'from ['+ @ tblname +'] where' + @ strwhere +'' + @ strorder
Else
Set @ strsql = 'select top '+ STR (@ pagesize) + ''+ @ strgetfields +' from ['+ @ tblname +'] '+ @ strorder
-- Execute the above Code on the first page, which will speed up the execution.
End
Else
Begin
-- The following code gives @ strsql the SQL code to be actually executed
Set @ strsql = 'select top '+ STR (@ pagesize) + ''+ @ strgetfields +' from ['+ @ tblname +']
Where ['+ @ fldname +'] '+ @ strtmp +' (['+ @ fldname +'])
From (select top '+ STR (@ PageIndex-1) * @ pagesize) +' ['+ @ fldname +']
From ['+ @ tblname +'] '+ @ strorder + ')
As tbltmp) '+ @ strorder
If @ strwhere! =''
Set @ strsql = 'select top '+ STR (@ pagesize) + ''+ @ strgetfields +' from ['+ @ tblname +']
Where ['+ @ fldname +'] '+ @ strtmp +' (['+ @ fldname +'])
From (select top '+ STR (@ PageIndex-1) * @ pagesize) +' ['+ @ fldname +']
From ['+ @ tblname +']
Where '+ @ strwhere + ''+ @ strorder + ')
As tbltmp) and '+ @ strwhere + ''+ @ strorder
End
End
Exec (@ strsql)
Go

/*
-- Parameters to be passed
@ Tblname varchar (255), -- table name
@ Strgetfields varchar (1000) = '*', -- the column to be returned
@ Fldname varchar (255) = '', -- Name of the sorted Field
@ Pagesize Int = 10, -- page size
@ Pageindex Int = 1, -- page number
@ Docount bit = 0, -- returns the total number of records. If the value is not 0, the system returns
@ Ordertype bit = 0, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ Strwhere varchar (1500) = ''-- Query condition (Note: Do not add where)
-- Call Test

Exec pagination @ tblname = 'jobs', @ strgetfields = 'job _ id, job_desc, min_lvl, max_lvl ',
@ Fldname = 'job _ id', @ pagesize = 3, @ pageindex = 1,
@ Docount = 0, @ ordertype = 1, @ strwhere =''
*/
========================================================== ====

Create proc sp_pagelist
@ Tbname sysname, -- Name of the table to be displayed by PAGE
@ Fieldkey sysname, which is used to locate the record's primary key (unique key) field and can only be a single Field
@ Pagecurrent Int = 1, -- the page number to be displayed
@ Pagesize Int = 10, -- size of each page (number of records)
@ Fieldshow nvarchar (1000) = '', -- List of fields to be displayed separated by commas (,). If this parameter is not specified, all fields are displayed.
@ Fieldorder nvarchar (1000) = '', -- List of sorting fields separated by commas (,). You can specify DESC/ASC after a field.
-- Used to specify the sorting order
@ Where nvarchar (1000) = '', -- Query Condition
@ Recordcount int output, -- total number of records
@ Pagecount int output -- total number of pages
As
Declare @ SQL nvarchar (4000)
Set nocount on
-- Check whether the object is valid
If object_id (@ tbname) is null
Begin
Raiserror (n' object "% s" does not exist ', @ tbname)
Return
End
If objectproperty (object_id (@ tbname), N 'istable') = 0
And objectproperty (object_id (@ tbname), N 'isview') = 0
And objectproperty (object_id (@ tbname), n'istablefunction') = 0
Begin
Raiserror (n' "% s" is not a table, view, or table value function ', @ tbname)
Return
End

-- Paging field check
If isnull (@ fieldkey, n'') =''
Begin
Raiserror (n' primary key (or unique key) required for paging processing)
Return
End

-- Other parameter checks and specifications
If isnull (@ pagecurrent, 0) <1 Set @ pagecurrent = 1
If isnull (@ pagesize, 0) <1 Set @ pagesize = 10
If isnull (@ fieldshow, n') = n' set @ fieldshow = n '*'
If isnull (@ fieldorder, n'') = n''
Set @ fieldorder = n''
Else
Set @ fieldorder = n 'ORDER BY' + ltrim (@ fieldorder)
If isnull (@ where, n'') = n''
Set @ where = n''
Else
Set @ where = n' where ('+ @ where + N ')'

-- 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
Set @ SQL = n' select @ pagecount = count (*)'
+ N' from' + @ tbname
+ N'' + @ where
Exec sp_executesql @ SQL, n' @ pagecount int output', @ pagecount output
Set @ recordcount = @ pagecount
Set @ pagecount = (@ pagecount + @ PageSize-1)/@ pagesize
End

-- Calculate the topn value displayed by PAGE
Declare @ topn varchar (20), @ topn1 varchar (20)
Select @ topn = @ pagesize,
@ Topn1 = @ pagecurrent * @ pagesize

-- Display directly on the first page
If @ pagecurrent = 1
Exec (N 'select top' + @ topn
+ N' + @ fieldshow
+ N' from' + @ tbname
+ N'' + @ where
+ N'' + @ fieldorder)
Else
Begin
Select @ pagecurrent = @ topn1,
@ SQL = n' select @ n = @ n-1, @ s = case when @ n <'+ @ topn
+ N'then @ s + n'', ''+ quotename (rtrim (cast ('+ @ fieldkey
+ N'as varchar (8000), n''''') else n'''''end from' + @ tbname
+ N'' + @ where
+ N' + @ fieldorder
Set rowcount @ pagecurrent
Exec sp_executesql @ SQL,
N' @ n int, @ s nvarchar (4000) output ',
@ Pagecurrent, @ SQL output
Set rowcount 0
If @ SQL = n''
Exec (n' select Top 0'
+ N' + @ fieldshow
+ N' from' + @ tbname)
Else
Begin
Set @ SQL = stuff (@ SQL, n '')
-- Execute Query
Exec (N 'select top' + @ topn
+ N' + @ fieldshow
+ N' from' + @ tbname
+ N 'where' + @ fieldkey
+ N' in ('+ @ SQL
+ N') '+ @ fieldorder)
End
End
Go