分页存储过程

USE [a6756475746]

GO

/****** Object:  StoredProcedure [dbo].[tbl_order_SearchWhereAndPage]    Script Date: 11/01/2011 09:37:39 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[tbl_order_SearchWhereAndPage]

    @AllCount   int OUTPUT,

    @PageIndex  int,

    @PageSize   int ,

    @minDate   datetime,

    @maxDate   datetime

AS

begin

    DECLARE @PageLower int

     set @PageLower=@PageSize * @PageIndex

    DECLARE @PageUpper int

     set @PageUpper= @PageLower + @PageSize - 1

    DECLARE @SearchSQL nvarchar(4000)

     set @SearchSQL='SELECT * ,( ROW_NUMBER() OVER (ORDER BY [ID] DESC) -1 ) AS RowNumber FROM tbl_order WHERE  (1=1) '

    DECLARE @SearchSQLCount nvarchar(4000)

     set @SearchSQLCount='SELECT @count=Count(*) FROM tbl_order WHERE (1=1) '

    declare @Result   [varchar](5000)

     set @Result=''

    if @minDate>convert(datetime,'1900-1-2')

    begin

        set @Result=@Result+' and oDeliveryDate >= '''+convert(varchar(20),@minDate)+''''

    end

    if @maxDate > convert(datetime,'1900-1-2')

    begin

        set @Result=@Result+' and oDeliveryDate <= '''+convert(varchar(20),dateadd(dd,1,@maxDate))+''''

    end

    set @SearchSQLCount=@SearchSQLCount+@Result

    set @SearchSQL=@SearchSQL+@Result

    SET @SearchSQL = 'WITH t AS (' + @SearchSQL +' )

    SELECT * FROM  t

    WHERE [RowNumber] BETWEEN '+ convert(varchar(50),@PageLower) +'  AND '+ convert(varchar(50),@PageUpper) + '

    ORDER BY RowNumber '

    exec (@SearchSQL)

    exec sp_executesql @SearchSQLCount ,N'@count as int out' ,@AllCount out

    print @SearchSQL

print @AllCount

end

exec (@Result)