存储过程分页

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