用存储过程实现翻页

来源：互联网发布：叶根友特楷简体 mac 编辑：程序博客网时间：2024/05/04 04:41

USE [CARD_DB]
GO
/****** Object: StoredProcedure [dbo].[pr_get_star_user_list_v2]    Script Date: 03/05/2010 15:27:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
查询STAR用户记录
最后更新：2009-11*/
ALTER PROCEDURE [dbo].[pr_get_star_user_list_v2]
@date bigint, -- 时间分页用
@forward int, -- 为0(首页)/1是下翻页，2/3(末页)上翻页
@num int,
@type tinyint
AS
begin
if(@type =0)
begin
-- 首页
if @forward = 0
select top (@num) type,uid,level,nickname,room_name,bank_name,bank_branch_name,bank_user_name,bank_account,tel,create_date
  from t_staruser with(nolock) order by create_date desc
-- 向下翻页
if @forward = 1
select top (@num) type,uid,level,nickname,room_name,bank_name,bank_branch_name,bank_user_name,bank_account,tel,create_date
  from t_staruser with(nolock) where create_date < @date order by create_date desc
-- 向上翻页
if @forward = 2
select * from (
  select top (@num) type,uid,level,nickname,room_name,bank_name,bank_branch_name,bank_user_name,bank_account,tel,create_date
   from t_staruser with(nolock) where create_date > @date order by create_date
  ) x
  order by create_date desc

-- 尾页
if @forward = 3
select * from (
  select top (@num) type,uid,level,nickname,room_name,bank_name,bank_branch_name,bank_user_name,bank_account,tel,create_date
   from t_staruser with(nolock) order by create_date
  ) x
  order by create_date desc
end
else
begin
-- 首页
if @forward = 0
select top (@num) type,uid,level,nickname,room_name,bank_name,bank_branch_name,bank_user_name,bank_account,tel,create_date
  from t_staruser with(nolock) where type=@type order by create_date desc
-- 向下翻页
if @forward = 1
select top (@num)type,uid,level,nickname,room_name,bank_name,bank_branch_name,bank_user_name,bank_account,tel,create_date
  from t_staruser with(nolock) where type=@type and create_date < @date order by create_date desc
-- 向上翻页
if @forward = 2
select * from (
  select top (@num) type,uid,level,nickname,room_name,bank_name,bank_branch_name,bank_user_name,bank_account,tel,create_date
   from t_staruser with(nolock) where type=@type and create_date > @date order by create_date
  ) x
  order by create_date desc

-- 尾页
if @forward = 3
select * from (
  select top (@num) type,uid,level,nickname,room_name,bank_name,bank_branch_name,bank_user_name,bank_account,tel,create_date
   from t_staruser with(nolock) where type=@type order by create_date
  ) x
  order by create_date desc
end
end