sql2005 存储过程分页示例代码

复制代码 代码如下:–分页存储过程示例
Alter PROCEDURE [dbo]澳门金沙vip,.[JH_PageDemo] @pageSize int = 9000000000,
@pageIndex int = 1 , @orderBy Nvarchar(200) = ” — 不加order By AS SET
NOCOUNT ON –声明变量 DECLARE @select VARCHAR(3048); DECLARE @from
VARCHAR(512); DECLARE @RowNumber VARCHAR(256); DECLARE @condition
nVARCHAR(3990); DECLARE @groupBy varchar(50); DECLARE @sql
VARCHAR(3998); DECLARE @RowStartIndex INT; DECLARE @RowEndIndex INT;
BEGIN SET NOCOUNT on IF @orderBy ” Set @orderBy = ‘ ORDER BY ‘ +
@orderBy; else Set @orderBy = ‘ ORDER BY Userid ‘ ; SET @select = ‘
select userid,username ,’; –设置排序语句 SET @RowNumber =’ROW_NUMBER()
OVER (‘ + @orderBy + ‘ ) as RowNumber ‘; SET @select = @select +
@RowNumber; SET @from = ‘ FROM users ‘; –设置条件语句@GULevel SET
@condition = ‘ WHERE 1=1 ‘; SET @condition = @condition + ‘AND userid
0’; –分组语句 SET @groupBy = ‘ GROUP BY USerID ‘ SET @RowStartIndex = (
@pageIndex -1) * @pageSize + 1 SET @RowEndIndex = @pageIndex *
@pageSize ; –查询结果 SET @sql = ‘SET NOCOUNT ON; WITH ResultTable AS (
‘ + @select + @from + @condition +’) SELECT * FROM ResultTable WHERE
RowNumber between ‘ + Cast(@RowStartIndex AS VARCHAR(32)) + ‘ AND ‘ +
CAST(@RowEndIndex AS VARCHAR(32)) + ‘ ; SELECT count(*) as totalcount ‘

这两天看了看分页方面的知识,我把园子里大侠们常用的分享下:
1.分页存储过程

  • @from + @condition + ‘ ‘ –PRINT @sql; EXEC(@sql); END

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:  <冯俊杰>
— Create date: <2009-08-27>
— Description: <通用分页存储过程>
— =============================================
 
alter PROCEDURE [dbo].[proc_ListPage]
(
@tblName     nvarchar(200),        —-要显示的表或多个表的连接
@fldName     nvarchar(500) = ‘*’,    —-要显示的字段列表
@pageSize    int = 10,        —-每页显示的记录个数
@page        int = 1,        —-要显示那一页的记录
@fldSort    nvarchar(200) = NULL,    —-排序字段列表或条件
@Sort        bit = 0,       
—-排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)–程序传参如’Sort
A asc’
@strCondition    nvarchar(1000) = NULL,    —-查询条件,不需要where
@ID        nvarchar(150),        —-主表的主键
@Dist      bit = 0, —-是否添加查询字段的 DISTINCT默认为0不添加/1添加
@pageCount    int = 1 output,            —-查询结果分页后的总页数
@Counts    int = 1 output,              —-查询到的记录数
@Curpage int=1 output—-当前页码
)
AS
SET NOCOUNT ON
 
DECLARE @sqlTmp nvarchar(1000)        —-存放动态生成sql语句
DECLARE @strTmp nvarchar(1000)       
—-存放取得查询结果总数的查询语句
DECLARE @strID     nvarchar(1000)       
—-存放取得查询开头或结尾ID的查询语句

DECLARE @strSortType nvarchar(10)    —-数据排序规则A
DECLARE @strFSortType nvarchar(10)    —-数据排序规则B

DECLARE @SqlSelect nvarchar(50)        
—-对含有DISTINCT的查询sql进行构造
DECLARE @SqlCounts nvarchar(50)         
—-对含有DISTINCT的总数查询进行构造

IF @Dist  = 0
BEGIN
    SET @SqlSelect = ‘select ‘
    SET @SqlCounts = ‘Count(0)’
END
ELSE
BEGIN
    SET @SqlSelect = ‘select distinct ‘
    SET @SqlCounts = ‘Count(DISTINCT '+@ID+')'
END

IF @Sort=0
BEGIN
    SET @strFSortType=’ ASC ‘
    SET @strSortType=’ DESC ‘
END
ELSE
BEGIN
    SET @strFSortType=’ DESC ‘
    SET @strSortType=’ ASC ‘
END