Create proc sp_PublicTurnPageWebSite (
@ TBName nvarchar (100) = '', -- table name, such as pinyin
@ PageSize int = 10, -- number of records per page. The default value is 10.
@ CurPage int = 1, -- indicates the current page 1
@ KeyField nvarchar (100) = 'id', -- key field name. The default value is ID. This field must be an index in a table or a field without duplicates or blank fields.
@ KeyAscDesc nvarchar (4) = 'asc ', -- ascending or descending of keywords. The default value is ascending ASC, and descending is DESC.
@ Fields nvarchar (500) = '*', -- specifies the selected column name. All columns are selected by default.
@ Condition nvarchar (200) = '', -- where Condition, empty by default
@ Order nvarchar (200) = ''-- sorting condition. The default value is null.
) With encryption
If @ TBName =''
Begin
Raiserror ('Please specify the table name! ', 11,1)
Return
End
If @ PageSize <= 0 or @ CurPage <0
Begin
Raiserror ('the number of current pages and records on each page must be greater than zero! ', 11,1)
Return
End
If @ KeyAscDesc = 'desc'
Set @ KeyAscDesc = '<'
Else
Set @ KeyAscDesc = '>'
If @ Condition <>''
Set @ Condition = 'where' + @ Condition
Declare @ SQL nvarchar (2000)
Set @ SQL =''
If @ CurPage = 1
Set @ SQL = @ SQL + 'select top' + cast (@ PageSize as nvarchar (20 )) + ''+ @ Fields + 'from' + @ TBName + @ Condition +'' + @ Order
Else
Begin
Declare @ iTopNum int
Set @ iTopNum = @ PageSize * (@ CurPage-1)
Set @ SQL = @ SQL + 'Clare @ sLastValue nvarchar (100) '+ char (13)
Set @ SQL = @ SQL + 'select top' + cast (@ iTopNum as nvarchar (20 )) + '@ sLastValue =' + @ KeyField + 'from' + @ TBName + @ Condition + ''+ @ Order + char (13)
Declare @ Condition2 nvarchar (200)
If @ Condition =''
Set @ Condition2 = 'where' + @ KeyField + @ KeyAscDesc + '@ sLastValue'
Else
Set @ Condition2 = 'and' + @ KeyField + @ KeyAscDesc + '@ slastvalue'
Set @ SQL = @ SQL + 'select top' + cast (@ PageSize as nvarchar (20 )) + ''+ @ Fields + 'from' + @ TBName + @ Condition + @ Condition2 + @ Order
End
EXECUTE sp_executesql @ SQL