分页存储过程

CREATE PROCEDURE [spDo_PagingOrderFilter] 
	@SetName varchar(40),			-- 表或视图的名字
	@PageIndex int,					-- 第几页
	@PageSize int,					-- 页大小
	@OrderExpression varchar(100),	-- 排序表达式(Order By ...)
	@FilterExpression varchar(200), -- 过滤条件(Where ...)
	@TotalSize int output			-- 总记录数
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @strSql nvarchar(2000)

	SET @strSql = 'SET @TotalSizeOut = (SELECT COUNT(*) FROM [' + @SetName + '] ' + @FilterExpression + ')';
	EXEC SP_EXECUTESQL @strSql,N'@TotalSizeOut int output',@TotalSizeOut=@TotalSize output;

	SET @strSql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (' + @OrderExpression + ') AS RowNum,* FROM [' + @SetName + '] ' + @FilterExpression + ')';
	SET @strSql = @strSql + ' AS T WHERE RowNum BETWEEN ' + STR((@PageIndex-1)*@PageSize+1) + ' AND ' + STR(@PageIndex*@PageSize);
    
    EXEC(@strSql)
END