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