mssqlserver存储过程分页

下面分享一个网友写的mssqlserver存储过程分页有需要的朋友可参考参考。

代码如下

复制代码

USE [BeyondDB] GO /****** Object:  StoredProcedure [dbo].[Y_Paging]    Script Date: 02/22/2013 14:53:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[Y_Paging] (     @TableName VARCHAR(max)=null,     --表名               @FieldList VARCHAR(max)=null,    --显示列名，如果是全部字段则为*               @PrimaryKey VARCHAR(max)=null,    --单一主键或唯一值键               @Where NVARCHAR(max)=null,        --查询条件 不含'where'字符，如id>10 and len(userid)>9               @Order VARCHAR(max)=null,        --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc                  @SortType INT=null,               --排序规则 1:正序asc 2:倒序desc 3:多列排序方法               @RecorderCount INT=null,          --记录总数 0:会返回总记录               @PageSize INT=null,               --每页输出的记录数               @PageIndex INT=null,              --当前页数           @Keyword varchar(max)=null,       --关键字     @FieldOne varchar(max)=null,       --字段1     @FieldTwo varchar(max)=null,       --字段2     @TotalCount INT OUTPUT,      --记返回总记录               @TotalPageCount INT OUTPUT   --返回总页数          ) as begin       DECLARE @sql NVARCHAR(max);     DECLARE @totalSql NVARCHAR(max);                            if(@Keyword is not null and @Keyword !='')     begin         if  ISNULL(@FieldOne,'') != ''          set @Order=@Order+' , (case when charindex('''+replace(@Keyword,' ',''','+@FieldOne+')>0 then 1 else 0 end)+(case when charindex(''')+''','+@FieldOne+')>0 then 1 else 0 end) '         if  ISNULL(@FieldOne,'') != ''         set @Order=@Order+' , (case when charindex('''+replace(@Keyword,' ',''','+@FieldTwo+')>0 then 1 else 0 end)+(case when charindex(''')+''','+@FieldTwo+')>0 then 1 else 0 end) '         end           if(@SortType is not null and @SortType=1)         set @Order=@Order+' asc '     if(@SortType is not null and @SortType=2)         set @Order=@Order+' desc '       SET @sql = ' WITH LIST AS                  (                        SELECT  ' + @FieldList + ',ROW_NUMBER() OVER (ORDER BY ' + @Order + ') as RowNumber                     FROM ' + @TableName + '                      WHERE 1=1 ' + @Where + '                                        )                   SELECT * FROM LIST WHERE RowNumber BETWEEN ' + STR(@PageIndex+1) + ' AND ' + STR (@PageIndex + @PageSize)                                           set @totalSql = ' SELECT  @TOTALCOUNT=COUNT(*) FROM ' + @TableName + ' WHERE 1=1 ' + @Where                                    print(@Sql)     EXEC(@Sql)                          --EXEC sp_executesql  @totalSql,N'     --                          @ID uniqueidentifier,                 --              @StatusList varchar(max),                 --              @BeginTime datetime,                 --              @EndTime datetime,                 --              @TitleOrNo varchar(max),                 --              @Excutor  uniqueidentifier,                 --              @Assignor uniqueidentifier,     --                            @TotalCount int output     --                            ',     --                            @ID  ,                 --              @StatusList  ,                 --              @BeginTime  ,                 --              @EndTime ,                 --              @TitleOrNo ,                 --              @Excutor,                 --              @Assignor,     --                            @TotalCount output end   --调用实例 USE [BeyondDB] GO   DECLARE @return_value int,         @TotalCount int,         @TotalPageCount int   EXEC    @return_value = [dbo].[Y_Paging]         @TableName = N'Account',         @FieldList = N'*',         @PrimaryKey = N'id',         @Where = N' and 1=1',         @Order = N' CreateTime ',         @SortType =2,         @PageSize = 5,         @PageIndex = 0,         @RecorderCount = null,         @Keyword = N'1',         @FieldOne = N'Accountname',         @FieldTwo = N'accountid',         @TotalCount = @TotalCount OUTPUT,         @TotalPageCount = @TotalPageCount OUTPUT   SELECT  @TotalCount as N'@TotalCount',         @TotalPageCount as N'@TotalPageCount'   SELECT  'Return Value' = @return_value   GO