create proc sp_PublicTurnPageWebSite(
@TBName nvarchar(100)=\'\',--表名,如 pinyin
@PageSizeint=10,--每页的记录数,默认为 10
@CurPageint=1,--表示当前页 1
@KeyFieldnvarchar(100)=\'ID\',--关键字段名,默认为 ID,该字段要求是表中的索引 或 无重复和不为空的字段
@KeyAscDescnvarchar(4)=\'ASC\',--关键字的升、降序,默认为升序 ASC , 降序为 DESC
@Fieldsnvarchar(500)=\'*\',--所选择的列名,默认为全选
@Conditionnvarchar(200)=\'\',--where 条件,默认为空
@Ordernvarchar(200)=\'\'--排序条件,默认为空
) with encryption as
if @TBName = \'\'
begin
raiserror(\'请指定表名!\',11,1)
return
end
if @PageSize <=0 or @CurPage <0
begin
raiserror(\'当前页数和每页的记录数都必须大于零!\',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 + \'declare @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