78 lines
5.6 KiB
HTML
78 lines
5.6 KiB
HTML
|
||
|
||
<div id="sina_keyword_ad_area2" class="articalContent ">
|
||
<div>-- =============================================</div>
|
||
<div>-- Description: <wbr> <wbr><高效分页存储过程，适用于Sql2005以上></div>
|
||
<div>-- Notes: <wbr> <wbr> <wbr> <wbr><排序字段强烈建议建索引></div>
|
||
<div>-- Page2005'dyj_vclient','client_id,last_dt','client_id','last_dt','desc','',20,1,0</div>
|
||
<div>-- =============================================</div>
|
||
<div>CREATE Procedure [dbo].[sp_page] <wbr></div>
|
||
<div> <wbr>@TableName varchar(2000), <wbr> <wbr> <wbr> <wbr> <wbr> --表名</div>
|
||
<div> <wbr>@Fields varchar(2000) = '*', <wbr> <wbr> <wbr>--字段名(全部字段为*)</div>
|
||
<div> <wbr>@PrimaryKey varchar(50), <wbr> <wbr> <wbr> <wbr> <wbr>--主键</div>
|
||
<div> <wbr>@OrderField varchar(500), <wbr> <wbr> <wbr> <wbr>--排序字段(必须!支持多字段)</div>
|
||
<div> <wbr>@sqlWhere varchar(500) = Null, <wbr> --条件语句(不用加where)</div>
|
||
<div> <wbr>@pageSize int, <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>--每页多少条记录</div>
|
||
<div> <wbr>@pageIndex int = 1 , <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>--指定当前为第几页</div>
|
||
<div> <wbr>@TotalPage int output <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>--返回总页数 <wbr></div>
|
||
<div>as</div>
|
||
<div>begin</div>
|
||
<div> <wbr></div>
|
||
<div> <wbr> <wbr> Begin Tran --开始事务</div>
|
||
<div> <wbr></div>
|
||
<div> <wbr> <wbr> Declare @sqlnvarchar(4000);</div>
|
||
<div> <wbr> <wbr> Declare @totalRecordint; <wbr> <wbr></div>
|
||
<div> <wbr> <wbr> ifisnull(@PrimaryKey,'')=''</div>
|
||
<div> <wbr> <wbr> <wbr> <wbr>set @PrimaryKey='*'</div>
|
||
<div> <wbr> <wbr> --计算总记录数</div>
|
||
<div> <wbr> <wbr> <wbr> <wbr> <wbr></div>
|
||
<div> <wbr> <wbr> if (@SqlWhere='' or@sqlWhere=NULL)</div>
|
||
<div> <wbr> <wbr> <wbr> <wbr> set @sql = 'select @totalRecord = count('+@PrimaryKey +') from ' + @TableName</div>
|
||
<div> <wbr> <wbr> else</div>
|
||
<div> <wbr> <wbr> <wbr> <wbr> set @sql = 'select @totalRecord = count('+@PrimaryKey +') from ' + @TableName + ' where ' + @sqlWhere</div>
|
||
<div> <wbr></div>
|
||
<div> <wbr> <wbr> EXEC sp_executesql@sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 <wbr> <wbr> <wbr> <wbr></div>
|
||
<div> <wbr> <wbr> <wbr></div>
|
||
<div> <wbr> <wbr> --计算总页数</div>
|
||
<div> <wbr> <wbr> select@TotalPage=CEILING((@totalRecord+0.0)/@PageSize)</div>
|
||
<div> <wbr></div>
|
||
<div> <wbr> <wbr> if (@SqlWhere='' or@sqlWhere=NULL)</div>
|
||
<div> <wbr> <wbr> <wbr> <wbr> set @sql = 'Select * FROM (select ROW_NUMBER()Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from '+ @TableName <wbr></div>
|
||
<div> <wbr> <wbr> else</div>
|
||
<div> <wbr> <wbr> <wbr> <wbr> set @sql = 'Select * FROM (select ROW_NUMBER()Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from '+ @TableName + ' where ' + @SqlWhere <wbr> <wbr></div>
|
||
<div> <wbr> <wbr> <wbr> <wbr> <wbr></div>
|
||
<div> <wbr> <wbr> <wbr></div>
|
||
<div> <wbr> <wbr> --处理页数超出范围情况</div>
|
||
<div> <wbr> <wbr> if@PageIndex<=0 <wbr></div>
|
||
<div> <wbr> <wbr> <wbr> <wbr> Set @pageIndex = 1</div>
|
||
<div> <wbr> <wbr> <wbr></div>
|
||
<div> <wbr> <wbr> if@pageIndex>@TotalPage</div>
|
||
<div> <wbr> <wbr> <wbr> <wbr> Set @pageIndex = @TotalPage</div>
|
||
<div> <wbr></div>
|
||
<div> <wbr> <wbr> <wbr>--处理开始点和结束点</div>
|
||
<div> <wbr> <wbr> Declare @StartRecordint</div>
|
||
<div> <wbr> <wbr> Declare @EndRecordint</div>
|
||
<div> <wbr> <wbr> <wbr></div>
|
||
<div> <wbr> <wbr> set @StartRecord =(@pageIndex-1)*@PageSize + 1</div>
|
||
<div> <wbr> <wbr> set @EndRecord =@StartRecord + @pageSize - 1</div>
|
||
<div> <wbr></div>
|
||
<div> <wbr> <wbr> --继续合成sql语句</div>
|
||
<div> <wbr> <wbr> set @Sql = @Sql + ') as[page_result_table] where rowId between ' +Convert(varchar(50),@StartRecord) + ' and ' + <wbr>Convert(varchar(50),@EndRecord)</div>
|
||
<div> <wbr> <wbr> <wbr></div>
|
||
<div> <wbr> <wbr> Exec(@Sql)</div>
|
||
<div> <wbr> <wbr>---------------------------------------------------</div>
|
||
<div> <wbr> <wbr> If @@Error<> 0</div>
|
||
<div> <wbr> <wbr> <wbr>Begin</div>
|
||
<div> <wbr> <wbr> <wbr> <wbr> RollBack Tran</div>
|
||
<div> <wbr> <wbr> <wbr> <wbr> Return -1</div>
|
||
<div> <wbr> <wbr> <wbr>End</div>
|
||
<div> <wbr> <wbr> <wbr>Else</div>
|
||
<div> <wbr> <wbr> <wbr>Begin</div>
|
||
<div> <wbr> <wbr> <wbr> <wbr> Commit Tran</div>
|
||
<div> <wbr> <wbr> <wbr> <wbr> Return @totalRecord ---返回记录总数</div>
|
||
<div> <wbr> <wbr> <wbr> End <wbr> <wbr></div>
|
||
<div>end</div>
|
||
<div><br>
|
||
</div>
|
||
</div>
|
||
|