createprocsp_PublicTurnPageWebSite( @TBNamenvarchar(100)=”,–表名,如pinyin @PageSizeint=10,–每页的记录数,默认为10 @CurPageint=1,–表示当前页1 @KeyFieldnvarchar(100)=’ID’,–关键字段名，默认为ID,该字段要求是表
create proc sp_PublicTurnPageWebSite( @TBName nvarchar(100)=”, –表名,如 pinyin @PageSize int=10, –每页的记录数,默认为 10 @CurPage int=1, –表示当前页 1 @KeyField nvarchar(100)=’ID’, –关键字段名，默认为 ID,该字段要求是表中的索引 或 无重复和不为空的字段 @KeyAscDesc nvarchar(4)=’ASC’, –关键字的升、降序，默认为升序 ASC , 降序为 DESC @Fields nvarchar(500)=’*’, –所选择的列名，默认为全选 @Condition nvarchar(200)=”, –where 条件,默认为空 @Order nvarchar(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
