USE [CDP]
GO
/****** Object:  StoredProcedure [dbo].[CustomPageList]    Script Date: 07/29/2014 14:28:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




------------------------------------

ALTER PROCEDURE [dbo].[CustomPageList]
    @tblName      nvarchar(2000),       -- 表名
    @fldName      nvarchar(2000),       -- 主键字段名
    @sortCondition     nvarchar(2000),       -- 主键字段名 id desc
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @ReCount      int=0 output,            -- 返回记录总数, 非 0 值则返回
    @pageCount      int=0 output,            -- 返回记录条数
    @strWhere     nvarchar(4000) = '', -- 查询条件 (注意: 不要加 where)
    @returnSql   nvarchar(4000)  output  -----最后返回的SQL语句
AS
declare @reSQL   nvarchar(2000) 
declare @strSQL   nvarchar(4000)       -- 主语句
declare @strTmp   nvarchar(2000)        -- 临时变量(查询条件过长时可能会出错，可修改100为1000)
declare @strOrder nvarchar(2000)        -- 排序类型

if @sortCondition is not null and @sortCondition != ''
begin
    set @strOrder = ' order by ' + @sortCondition +''
end

SET @strSQL = ' Select top ' + str(@PageSize) + ' row_number() over (order by id)as rowNumber,  * from ['
+ @tblName + '] where  '
+@fldName+' not in (Select  top ' + str((@PageIndex-1)*@PageSize) + ' '
+@fldName+' from '  + @tblName +' ' + @strOrder +')' + @strOrder 


if @strWhere is not null and @strWhere != ''
begin
SET @strSQL = ' Select top '+ str(@PageSize)+' row_number() over (order by id)as rowNumber, * from ['+ @tblName + '] where '+@fldName+' not in (Select top ' + str((@PageIndex-1)*@PageSize)+@fldName+' from ' + @tblName +' where '+ @strWhere +''+ @strOrder+') and '+ @strWhere +' '+ @strOrder
end

if @PageIndex = 1
if @strWhere is not null or @strWhere<>''
begin
    set @strTmp =''
    if @strWhere is not null and @strWhere<>''
    begin
        set @strTmp = ' where ' + @strWhere
    end
    else
    begin
     set @strTmp =''
    end
    set @strSQL = ' select top ' + str(@PageSize) + ' row_number() over (order by id)as rowNumber, * from [' + @tblName + ']' + @strTmp + ' ' + @strOrder
end

if @strWhere is not null and @strWhere<>''
set @reSQL = 'select @ReCount=count(*)  from [' + @tblName + ']'+' where ' + @strWhere
else
set @reSQL = 'select @ReCount=count(*)  from [' + @tblName + ']'
----取得查询结果总数量-----
exec sp_executesql @reSQL,N'@ReCount int out',@ReCount out 


-----返回sql-----
set @returnSql =@strSQL
-----返回pageCount-----
if @PageSize>0
begin
	if @ReCount%@PageSize=0
	begin
	set @pageCount=@ReCount/@PageSize
	end
	else
	begin
	set @pageCount=(@ReCount-(@ReCount%@PageSize))/@PageSize+1
	end
end





--print @strSQL
exec (@strSQL)