A more practical and large data paging stored procedure _mssql
Source: Internet
Author: User
Create proc Sp_publicturnpagewebsite (
@TBName nvarchar (100) = ',--table name, such as pinyin
@PageSize int=10,--Number of records per page, defaults to 10
@CurPage Int=1,--Represents the current page 1
@KeyField nvarchar = ' id '--key field name, default ID, which requires an index in a table or a field that is not repeated and not empty
@KeyAscDesc nvarchar (4) = ' ASC '--the keyword is ascending, descending, and the default is ASC, descending to DESC
@Fields nvarchar (500) = ' * ',--the name of the column selected, default to full selection
@Condition nvarchar = ',--where condition, default is NULL
@Order nvarchar (200) = '--sort criteria, default to NULL
) with encryption as
If @TBName = '
Begin
RAISERROR (' Please specify the table name! ', 11, 1)
Return
End
If @PageSize <=0 or @CurPage <0
Begin
RAISERROR (' The current page and the number of records per page must be greater than 0! ', 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) + "+ @Fields + ' from ' + @TBName + @Condition + '" + @Order
Else
Begin
DECLARE @iTopNum int
Set @iTopNum = @PageSize * (@CurPage-1)
Set @SQL = @SQL + ' declare @sLastValue nvarchar ' + char (13)
Set @SQL = @SQL + ' Select top ' + cast (@iTopNum as nvarchar) + ' @sLastValue = ' + @KeyField + ' from ' + @TBName + @Con dition + ' + @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) + "+ @Fields + ' from ' + @TBName + @Condition + @Con Dition2 + @Order
End
EXECUTE sp_executesql @SQL
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.