Sql Server分页存储过程

USE [bicycleDB]
GO
/****** 对象:  StoredProcedure [dbo].[UP_Sel_Bike_Paging]    脚本日期: 01/10/2011 18:23:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[UP_Sel_Bike_Paging]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) 
DROP PROCEDURE [dbo].[UP_Sel_Bike_Paging] 
GO
*/

-- ======================= SP label, do not delete ===============================
-- Date Created: 2010/12/10 17:09:48 
-- Execute: <ExecuteReaderOrDataSet> 
-- Table: <[dbo].[Bike]> 
-- SPType: <SelectPaging> 
-- Description: <分页获取自行车借还记录信息。> 
-- ===============================================================================

CREATE PROCEDURE [dbo].[UP_Sel_Rental_Paging] 
    @pageSize INT = 15 ,                           -- 单页显示的记录数 
    @pageIndex INT = 1 ,                           -- 当前页码 
    @cardCSN varchar(20),                           -- 物理卡号
    @numRecord INT = NULL OUTPUT                   -- 所有符合查询条件的总记录数 

AS
    SET NOCOUNT ON  

    DECLARE @pageLowerBound INT  
    DECLARE @pageUpperBound INT  

    IF(@pageSize>0)  
    BEGIN 
        SET @pageLowerBound=(@pageIndex-1) * @pageSize + 1  
        SET @pageUpperBound=@pageIndex * @pageSize 
    END 
    
    SELECT @numRecord = COUNT(*)
    FROM [dbo].[V_Rental] M                        --[dbo].[V_Rental] 为视图，因为查了两个表，也可以是一个表。那么就不用视图了。
     where M.cardcsn=@cardCSN

    SELECT bikeSN,cardCSN,rentalSN,machineSN,premoney,[money],operType,operDate,address 
    FROM 
    (
    SELECT V_Rental.*, ROW_NUMBER() OVER(ORDER BY V_Rental.rentalSN ASC) AS _pos
    FROM [dbo].[V_Rental]
    where V_Rental.cardcsn=@cardCSN
    ) 
    AS sp
    WHERE _pos BETWEEN @pageLowerBound AND @pageUpperBound 

    SET NOCOUNT OFF