话不多说,请看代码
------------------------------------- -----作者:张欣宇 -----时间:2013-06-28 -----简介:根据参数和条件分页查询 ------------------------------------- Createproc [dbo].[Up_PagingQueryByParameter] ( ----- 表名或能查询到结果的SQL语句{SQL语句左右必须有括号例:(select * from tbl1)} @TableNamevarchar(max), ----- 要查询的列名语句; 可空默认* @ColumnNamevarchar(5000), ----- 用来排序的列; 不可为空 @OrderByColumnNamevarchar(50), ----- 排序desc(倒序5.4.3.2.1)或asc(正序1.2.3.4.5); 可空默认asc @ShrtByvarchar(4), ----- Where条件; 可空默认1=1 @Wherevarchar(5000), ----- 每页显示数; 可空默认20 @PageShowsint, ----- 当前页数; 可空默认1 @CurrentPageint, ----- 0为分页查询;其他为查询全部; 可空默认0 @IsPagingint ) as begin ----- 参数检查及规范 ifisnull(@Where,N"")=N""set@Where= N"1=1"; ifisnull(@ColumnName,N"")=N""set@ColumnName = N"*"; ifisnull(@PageShows,0)<1set@PageShows = 20; ifisnull(@CurrentPage,0)<1set@CurrentPage = 1; ifisnull(@ShrtBy,N"")=N""set@ShrtBy ="asc"; ifisnull(@IsPaging,0)<>1set@IsPaging = 0; ----- 定义 -- 拼接的SQL语句 declare@SqlStr nvarchar(max); declare@SqlWithStr nvarchar(max); -- 开始条数 declare@StartIndexint; -- 结束条数 declare@EndIndexint; ----- 赋值 set@StartIndex = (@CurrentPage-1)*@PageShows+1; print(@CurrentPage); print(@PageShows); print(@StartIndex); set@EndIndex = @CurrentPage*@PageShows; print(@EndIndex); set@OrderByColumnName=@OrderByColumnName+" "+@ShrtBy; ----- 分页查询 set@SqlWithStr = N"with temp as(select ROW_NUMBER() over(order by "+@OrderByColumnName+N") as RowNumber,"+@ColumnName+N" from "+@TableName+N" as tableName where "+@Where+N")"; if(@IsPaging = 0) begin set@SqlStr = @SqlWithStr + N" select "+@ColumnName+N" from temp where temp.RowNumber between "+convert(nvarchar(20),@StartIndex)+N" and "+convert(nvarchar(20),@EndIndex)+N""; ---- print(@SqlStr); exec(@SqlStr); ----- 总数查询 set@SqlStr = @SqlWithStr + N" select count(*) as TotalNumber from temp"; ---- print(@SqlStr); exec(@SqlStr); end else begin set@SqlStr = @SqlWithStr + N" select "+@ColumnName+N" from temp"; ---- print(@SqlStr); exec(@SqlStr); end endhttp://m.bbyears.com/jiaocheng/116927.html
推荐访问: