分页 存储过程

 

/**********************************

// Author : 

// Create date : 2010-10-21

// Description : 分页

// Update Time : 

**********************************/

ALTER proc [dbo].[spa_entity_card_user_log_query_All]

@begin int,

@end int,

@begintime datetime,

@endtime datetime,

@type nvarchar(50),

@value nvarchar(50)

as

begin

declare @QueryString nvarchar(1000),@ParmDefinition NVARCHAR(200)

     declare @sqlCmd nvarchar(50)

set @sqlCmd=

case @type

when 1 then 'user_id=@Queryvalue and '   

when 2 then 'user_name=@Queryvalue and '

when 3 then 'card_num=@Queryvalue and '

else ''

end

set @QueryString='select a.card_num,a.[user_name],a.used_time,b.point,c.config_name,c.[money] from

(select * from (

select batch_id,card_num,[user_name],used_time,row_number() over(order by card_id) id 

from entity_card_used_log 

where '+@sqlCmd+'used_time>=@Querybegintime and used_time<@Queryendtime 

)  d 

where d.id > @Querybegin and d.id < @Queryend

) as a

join entity_card_batch b on a.batch_id=b.batch_id

join entity_card_config c on b.config_id=c.config_id 

select count(1) from entity_card_used_log 

where '+@sqlCmd+'used_time>=@Querybegintime and used_time<@Queryendtime'

set @ParmDefinition=N'@Querybegin int,@Queryend int,@Querybegintime datetime,

@Queryendtime datetime,@Queryvalue nvarchar(50)'

--print @QueryString

EXEC sp_executesql @QueryString,@ParmDefinition,

@Querybegin=@begin,@Queryend=@end,@Querybegintime=@begintime,

@Queryendtime=@endtime,@Queryvalue=@value

end

 

---------------------------------------------------------------------------------------------------------------

/**********************************

// Author : 

// Create date : 2010-10-21

// Description : 分页

// Update Time : 

**********************************/

ALTER proc [dbo].[spa_pagination_Query]

@pageNum int,  --每页显示数量

@page int    --页数

as

begin 

select top (@pageNum) * from tb_inf

where id not in

(

select top (@pageNum*(@page-1)) id from tb_inf order by id

)

order by id

select count(1) from tb_inf

end

GO