sql 分页存储过程

分享一个分页存储过程，性能不错，据测试，100万以内的数据无压力

 

USE [V8]
GO
/****** Object:  StoredProcedure [dbo].[V9pgComment]    Script Date: 01/21/2013 09:30:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[V9pgComment]
    -- 页码
    @pn    int,
    -- 页大小
    @ps    int,
    -- 总记录数
    @n    int output,
    -- 会员Id
    @memberid int
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @filter    nvarchar(1000)
    DECLARE @rows    nvarchar(1000)
    DECLARE    @cnt    nvarchar(1000)
    DECLARE    @sql    nvarchar(4000)
    
    -- 筛选条件
    SET @filter = ' AND V9Comment.V9MemberId=@memberid '
    -- 重新排序
    SET @rows = ' 
                WITH tb AS
                (
                    SELECT r_id=ROW_NUMBER() OVER(ORDER BY V9Comment.V9CommentId DESC),V9CommentId FROM V9Comment
                    WHERE 1=1 '+@filter+'
                )
                '
    -- 总记录数
    SET @cnt = ' SELECT @n=COUNT(1) FROM V9Comment WHERE 1=1 ' + @filter
    
    -- 查询列表
    SET @sql = '
                SELECT c.*,t.V9SourceType,t.V9SourceId,t.Title TopicTitle,t.SourceUrl Url,
                m.NickName, m.Face, m.ExpLevelVIP, m.ExpLevelExp, m.IsMale
                FROM V9Comment c INNER JOIN (SELECT * FROM tb WHERE r_id BETWEEN (@pn-1)*@ps+1 AND @pn*@ps) b
                ON c.V9CommentId=b.V9CommentId
                LEFT JOIN V9CommentTopic t 
                ON c.V9CommentTopicId=t.V9CommentTopicId INNER JOIN V9Member m 
                On c.V9MemberId = m.V9MemberId
                '
                
    SET @sql = @rows + @sql + @cnt
    PRINT @sql
    EXEC sp_executesql @sql,
    N'@n int output, @pn int, @ps int, @memberid int',
    @pn = @pn,
    @ps = @ps,
    @n = @n output,
    @memberid = @memberid
END