Collected paging stored procedures

 

I. Paging stored procedure with output parameters

-- Create a stored procedure
Create procedure [DBO]. [bin_getpageofrecordscount]
@ Tablename varchar (32), -- table name
@ Fields varchar (1024), -- field
@ Ordername varchar (32), -- Sort Field
@ Ordertype bit, -- sort type (descending if not 0)
@ Pageindex int, -- page index
@ Pagesize int, -- page size
@ Condition nvarchar (1024), -- Query condition (do not add where)
@ Count int output

As
Begin
Declare @ strsql nvarchar (2048) -- subject sentence
Declare @ strorder nvarchar (64) -- Sort statement

If @ condition! =''
Set @ strsql = 'select @ COUNT = count (*) from '+ @ tablename + 'where' + @ Condition
Else
Set @ strsql = 'select @ COUNT = count (*) from '+ @ tablename

Execute sp_executesql @ strsql, n' @ count int output', @ count output

If @ ordertype! = 0
Set @ strorder = 'ORDER BY' + @ ordername + 'desc'
Else
Set @ strorder = 'ORDER BY' + @ ordername + 'asc'

If @ pageindex = 1 -- the following code is executed on the first page at a high speed.
Begin
If @ condition! =''
Set @ strsql = 'select top '+ STR (@ pagesize) + ''+ @ fields + 'from' + @ tablename + 'where' + @ condition +'' + @ strorder
Else
Set @ strsql = 'select top '+ STR (@ pagesize) + ''+ @ fields + 'from' + @ tablename +'' + @ strorder
End
Else
Begin
If @ condition! =''
Set @ strsql = 'select' + @ fields + 'from
(
Select row_number () over ('+ @ strorder +') as rank, '+ @ fields + 'from' + @ tablename + 'where' + @ condition +'
) As temp
Where rank between '+ STR (@ PageIndex-1) * @ pagesize + 1) + 'and' + STR (@ pageindex * @ pagesize)
Else
Set @ strsql = 'select' + @ fields + 'from
(
Select row_number () over ('+ @ strorder +') as rank, '+ @ fields + 'from' + @ tablename +'
) As temp
Where rank between '+ STR (@ PageIndex-1) * @ pagesize + 1) + 'and' + STR (@ pageindex * @ pagesize)
End
Exec (@ strsql)
End

2. stored procedures without output parameters

Create procedure [DBO]. [bin_getpageofrecords]
@ Pagesize Int = 20, -- page size
@ Currentpage int, -- page number
@ Columns varchar (1000) = '*', -- the expected field
@ Tablename varchar (100), -- the table to be queried
@ Condition varchar (1000) = '', -- Query condition, where keyword not required
@ Asccolumn varchar (100) = '', -- Name of the sorted field (that is, order by column ASC/DESC)
@ Bitordertype bit = 0, -- sort type (0 is ascending, 1 is descending)
@ Pkcolumn varchar (50) = ''-- primary key name

As
Begin -- starts the Stored Procedure
Declare @ strtemp varchar (300)
Declare @ strsql varchar (5000) -- the last statement executed by the Stored Procedure
Declare @ strordertype varchar (1000) -- sort type Statement (order by column ASC or order by column DESC)

Begin
If @ bitordertype = 1 -- descending order
Begin
Set @ strordertype = 'ORDER BY' + @ asccolumn + 'desc'
Set @ strtemp = '<(select min'
End
Else -- Ascending
Begin
Set @ strordertype = 'ORDER BY' + @ asccolumn + 'asc'
Set @ strtemp = '> (select Max'
End

If @ currentpage = 1 -- first page
Begin
If @ condition! =''
Set @ strsql = 'select top '+ STR (@ pagesize) + ''+ @ columns + 'from' + @ tablename +
'Where' + @ condition + ''+ @ strordertype
Else
Set @ strsql = 'select top '+ STR (@ pagesize) + ''+ @ columns + 'from' + @ tablename + @ strordertype
End

Else -- other pages
Begin
If @ condition! =''
Set @ strsql = 'select top '+ STR (@ pagesize) + ''+ @ columns + 'from' + @ tablename +
'Where' + @ condition + 'and' + @ pkcolumn + @ strtemp + '(' + @ pkcolumn + ') '+' from (select top '+ STR (@ currentPage-1) * @ pagesize) +
''+ @ Pkcolumn + 'from' + @ tablename + 'where' + @ condition +'' + @ strordertype + ') as tabtemp)' + @ strordertype
Else
Set @ strsql = 'select top '+ STR (@ pagesize) + ''+ @ columns + 'from' + @ tablename +
'Where' + @ pkcolumn + @ strtemp + '(' + @ pkcolumn + ')' + 'from (select top' + STR (@ currentPage-1) * @ pagesize) + ''+ @ pkcolumn +
'From' + @ tablename + @ strordertype + ') as tabtemp)' + @ strordertype
End

End
Exec (@ strsql)
End