分页sql存储过程算法

/****** Object:  StoredProcedure [dbo].[PRO_Pub_FenYe]    Script Date: 08/04/2014 11:14:22 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

--suchao

--2014-04-27

--分页查找公共方法

*/

CREATE PROCEDURE [dbo].[PRO_Pub_FenYe]

    @AsyncSql NVARCHAR(MAX),

	@PageIndex int,

	@PageSize int,

	@RecordCount int output

AS

BEGIN

SET NOCOUNT ON

DECLARE @Sql NVARCHAR(MAX) --定义SQL语句

SET @Sql=@AsyncSql;

    DECLARE @pagesql NVARCHAR(MAX)

	SET @pagesql='SELECT * FROM ('+@Sql+') AS temp WHERE myrank  BETWEEN '+ CAST((@PageIndex-1)*@PageSize+1 AS NVARCHAR(10))+' AND '+CAST(@PageIndex*@PageSize AS NVARCHAR(10))+''

	exec sp_executesql  @pagesql --执行SQL语句返回当前页面数据

	DECLARE @recount NVARCHAR(MAX)

	SET @recount='SELECT @RecordCount=COUNT(1) FROM ('+@Sql+') AS temp'

	EXEC sp_executesql @recount,N'@RecordCount int output',@RecordCount output --返回受影响行数

	    print @RecordCount

	return @@error

END

GO