刚开始学习分页存储过程的时候看到有很多长篇大论的代码,看起来很乱,现在有一个很好的分页存储过程,分享一下:
ALTER PROCEDURE [dbo].[pr_page_helper]
@top INT=NULL, --- 返回前N位记录 -1 :全部
@ascOrDesc bit =NULL, ---1 :DESC 0:ASC
@Column NVARCHAR(2024) = NULL, ---返回的字段
@sqlWhere NVARCHAR(1024) = NULL, ---过滤条件
@orderBy NVARCHAR(1024) = NULL, --排序关键字 --还真不能空
@tableName NVARCHAR(100)=NULL, -- 表名
@currentPage INT = NULL, --页码
@pageSize INT = NULL, --一页的记录条数
@totalCount INT = NULL OUTPUT --记录总数
--@currentPage 和 @pageSize 同时不为空时则分页,其他则是返回前N个记录
AS
BEGIN
DECLARE @sql NVARCHAR(MAX)
DECLARE @tmp_sql NVARCHAR(MAX)
IF ISNULL(@currentpage,0)>0 AND ISNULL(@pagesize,0)>0
BEGIN
DECLARE @N_BEGIN INT
DECLARE @N_END INT
SET @N_BEGIN = @pageSize*(@currentPage-1)+1
SET @N_END = @pageSize*@currentPage
SET @tmp_sql = N'SELECT @pc1 = COUNT(*) '+N' FROM '+@tableName
IF NOT @sqlWhere IS NULL SET @tmp_sql = @tmp_sql + N' WHERE ' + @sqlWhere
EXECUTE sp_executesql @tmp_sql, N'@pc1 INT OUTPUT', @pc1= @totalCount OUTPUT
SET @sql = N' SELECT '+ @Column +N', ROW_NUMBER() OVER(ORDER BY ' + @orderBy
IF ISNULL(@ascOrDesc, 0) > 0
SET @sql = @sql + N' DESC ) AS ROW_NUMBER '
ELSE
SET @sql = @sql + N' ASC ) AS ROW_NUMBER '
SET @sql = @sql + N' FROM '+@tableName
IF NOT @sqlWhere IS NULL SET @sql = @sql + N' WHERE ' + @sqlWhere
SET @sql = N'WITH t AS(' + @sql + N') SELECT * FROM t WHERE t.ROW_NUMBER BETWEEN @between AND @end'
EXECUTE sp_executesql @sql, N'@between INT, @end INT', @between = @N_BEGIN, @end = @N_END
print @sql
print @tmp_sql
select @totalCount
END
ELSE
BEGIN
SET @sql = N'SELECT '
IF ISNULL(@top, 0) > 0 SET @sql = @sql + N' TOP ' + CAST(@top AS NVARCHAR) + N' '
SET @sql = @sql+ @Column + N' FROM '+@tableName
IF NOT @sqlWhere IS NULL SET @sql = @sql + N' WHERE ' + @sqlWhere
IF NOT @orderBy IS NULL
BEGIN
SET @sql = @sql + N' ORDER BY ' + @orderBy
IF ISNULL(@ascOrDesc, 0) > 0
SET @sql = @sql + N' DESC '
ELSE
SET @sql = @sql + N' ASC '
END
print @sql
EXECUTE sp_executesql @sql
END
END