sql分页存储过程(返回记录数)

if里面处理的是带搜索条件的
else里面处理的是不带搜索条件的




-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:andy
-- Create date: 2013.3.6
-- Description: 获取正在制作的课件分页存储过程
-- =============================================
CREATE PROCEDURE dbo.sp_vendor_coursewareMakingPagging
    @PageIndex INT ,
    @PageSize INT ,
    @CoursewareName NVARCHAR(256) = '' ,
    @VendorId INT ,
    @StartDate DATETIME = '' ,
    @EndDate DATETIME = ''
AS
    BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
        SET NOCOUNT ON;
        SET NOCOUNT ON;
        DECLARE @StartIndex INT;
        DECLARE @EndIndex INT;
        SET @PageIndex = @PageIndex - 1;
        SET @StartIndex = @PageIndex * @PageSize + 1;
        SET @EndIndex = ( @PageIndex + 1 ) * @PageSize;
--pager list
        IF ( @StartDate != ''
             AND @EndDate != ''
           )
            BEGIN
                WITH    a AS ( SELECT   [CoursewareId] ,
                                        [GroupGuid] ,
                                        [ParentId] ,
                                        [Version] ,
                                        [CoursewareName] ,
                                        [Description] ,
                                        [IsPublish] ,
                                        ec.[StatusId] ,
                                        [CoursewareTypeId] ,
                                        [VendorId] ,
                                        ec.[PictureListId] ,
                                        ec.[CreateUserId] ,
                                        ec.[CreateDate] ,
                                        ec.[ModifyUserId] ,
                                        ec.[ModifyDate] ,
                                        epl.URL ,
                                        ROW_NUMBER() OVER ( ORDER BY ec.CreateDate DESC ) AS row_num
                               FROM     [dbo].[E_Courseware] AS ec
                                        JOIN dbo.E_PictureList AS epl ON ec.PictureListId = epl.PictureListId
                               WHERE    ec.IsPublish = 0
                                        AND ec.VendorId = @VendorId
                                        AND ec.StatusId = 0
                                        AND ec.CoursewareName LIKE '%'
                                        + @CoursewareName + '%'
                                        AND ec.CreateDate BETWEEN @StartDate AND @EndDate
                             )
                    SELECT  *
                    FROM    a
                    WHERE   row_num BETWEEN @StartIndex AND @EndIndex;
--record count
                SELECT  ISNULL(COUNT(ec.CoursewareId), 0) AS RecourdCount
                FROM    [dbo].[E_Courseware] AS ec
                        JOIN dbo.E_PictureList AS epl ON ec.PictureListId = epl.PictureListId
                WHERE   ec.IsPublish = 0
                        AND ec.VendorId = @VendorId
                        AND ec.StatusId = 0
                        AND ec.CoursewareName LIKE '%' + @CoursewareName + '%'
                        AND ec.CreateDate BETWEEN @StartDate AND @EndDate
                   
            END
        ELSE
            BEGIN
                WITH    a AS ( SELECT   [CoursewareId] ,
                                        [GroupGuid] ,
                                        [ParentId] ,
                                        [Version] ,
                                        [CoursewareName] ,
                                        [Description] ,
                                        [IsPublish] ,
                                        ec.[StatusId] ,
                                        [CoursewareTypeId] ,
                                        [VendorId] ,
                                        ec.[PictureListId] ,
                                        ec.[CreateUserId] ,
                                        ec.[CreateDate] ,
                                        ec.[ModifyUserId] ,
                                        ec.[ModifyDate] ,
                                        epl.URL ,
                                        ROW_NUMBER() OVER ( ORDER BY ec.CreateDate DESC ) AS row_num
                               FROM     [dbo].[E_Courseware] AS ec
                                        JOIN dbo.E_PictureList AS epl ON ec.PictureListId = epl.PictureListId
                               WHERE    ec.IsPublish = 0
                                        AND ec.VendorId = @VendorId
                                        AND ec.StatusId = 0
                                        AND ec.CoursewareName LIKE '%'
                                        + @CoursewareName + '%'
                             )
                    SELECT  *
                    FROM    a
                    WHERE   row_num BETWEEN @StartIndex AND @EndIndex;
--record count
                SELECT  ISNULL(COUNT(ec.CoursewareId), 0) AS RecourdCount
                FROM    [dbo].[E_Courseware] AS ec
                        JOIN dbo.E_PictureList AS epl ON ec.PictureListId = epl.PictureListId
                WHERE   ec.IsPublish = 0
                        AND ec.VendorId = @VendorId
                        AND ec.StatusId = 0
                        AND ec.CoursewareName LIKE '%' + @CoursewareName + '%';
                 
            END
    END