分页SQL

USE [OumindBlog]
GO
/****** Object: StoredProcedure [dbo].[Usp_GetAlbByPage14] Script Date: 03/18/2015 23:37:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <JJH>
-- Create date: <Create Date,,>
-- Description: <得到相册分页数据>
-- =============================================
ALTER PROCEDURE [dbo].[Usp_GetAlbByPage14]
-- Add the parameters for the stored procedure here
@pageIndex int,
@pageSize int,
@rowCount int output
AS
BEGIN
--关闭sql语句的执行统计，提高性能
SET NOCOUNT ON;

--计算开始和结束的标签
declare @startIndex int,@endIndex int
set @startIndex =(@pageIndex-1)*@pageSize
set @endIndex=@pageIndex*@pageSize

--得到分页的数据
select * from (select rowId=(ROW_NUMBER() over(order by paid desc)),* from BlogPhotoAlblum where PaIsDel=0)
as t where t.rowId>@startIndex and t.rowId<=@endIndex

--获取数据总行数
select @rowCount=COUNT(1) from BlogPhotoAlblum where PaIsDel=0

END