A simple paging Control + Stored Procedure (source code)

I just applied for it a few days ago. Blog So I will sort out my previous items and release them these days.

This control has been written a long time ago and has never been released in its own space. In fact, this paging control has no special functions:

You can use the Stored Procedure pagination or the control's ownDatatablePage.

You can select the number of records by page, by no page, or by page.

You can select any number of pages to jump.

Download example and source code

Paging storage process:

/*
function name: getrecordfrompage
function: obtains data on a specified page.
parameter description:
@ tblname: name of the table containing data
@ pkname key field name
@ strgotfields: field to be obtained
@ pagesize number of records per page
@ pageindex page number to be retrieved
@ ordertype sorting type, 0-ascending, 1-descending
@ strwhere query condition (Note: Do not add where)
@ iscount whether to retrieve the number of records, 0-no, 1-obtain the
@ strsort sorting field
*/
Create procedure getrecordfrompage
@ tblname varchar (255 ), -- table name
@ pkname varchar (255), -- field name
@ strgotfields varchar (1000) = '*', -- query the field name
@ pagesize Int = 10, -- page size
@ pageindex Int = 1, -- page number
@ ordertype bit = 0, -- set the sorting type, non-0 values are sorted in descending order
@ strwhere varchar (2000) = '', -- Query condition (Note: Do not add where)
@ iscount bit = 1, -- Get the number of records
@ strsort varchar (255) = ''-- Sort field
as
declare @ strsql varchar (6000) -- subject sentence
declare @ strtmp varchar (1000) -- temporary variable
declare @ strorder varchar (500) -- sorting type
declare @ strcount varchar (1000)
declare @ fldname varchar (255)
declare @ sortname varchar (255)
declare @ countsql varchar (1000)

Set @ fldname = @ pkname
If @ strsort! =''
Begin
Set @ sortname = @ strsort
End
Else
Begin
Set @ sortname = @ pkname
End

If @ iscount = 1
Begin
If @ strwhere! =''
Begin
Set @ countsql = 'select count ('+ @ fldname +') from '+ @ tblname + 'where' + @ strwhere
End
Else
Begin
Set @ countsql = 'select count ('+ @ fldname +') from '+ @ tblname
End
Exec (@ countsql)
Return
End
Else
Begin

If @ ordertype! = 0
Begin
Set @ strtmp = '<(select min'
Set @ strorder = 'ORDER BY' + @ sortname + 'desc'
End
Else
Begin
Set @ strtmp = '> (select Max'
Set @ strorder = 'ORDER BY' + @ sortname + 'asc'
End

Set @ strsql = 'select top '+ STR (@ pagesize) + @ strgotfields + '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) + @ strgotfields + 'from'
+ @ Tblname + 'where' + @ fldname + ''+ @ strtmp + '('
+ @ Fldname + ') from (select top' + STR (@ PageIndex-1) * @ pagesize) +''
+ @ Fldname + 'from' + @ tblname + 'where' + @ strwhere +''
+ @ Strorder + ') as tbltmp) and' + @ strwhere + ''+ @ strorder

If @ pageindex = 1
Begin
Set @ strtmp =''
If @ strwhere! =''
Set @ strtmp = 'where ('+ @ strwhere + ')'

Set @ strsql = 'select top '+ STR (@ pagesize) + @ strgotfields + 'from'
+ @ Tblname + ''+ @ strtmp +'' + @ strorder
End

exec (@ strsql)
end
go