分页存储过程

 

/*
 *
 * author:        MomerY_L
 * update time:  2008-11-15
 *
 */
-- 新闻表

use LeafBookShop
go

-- 添加
CREATE PROCEDURE dbo.UP_News_ADD
@NewsId int output,
@Title varchar(100),
@NewContent varchar(MAX),
@RegisterDate datetime,
@AdminId int,
@IsValid bit

 AS
 INSERT INTO News(
 [Title],[NewContent],[RegisterDate],[AdminId],[IsValid]
 )VALUES(
 @Title,@NewContent,@RegisterDate,@AdminId,@IsValid
 )
 SET @NewsId = @@IDENTITY

GO
-- 更新
CREATE PROCEDURE dbo.UP_News_Update
@NewsId int,
@Title varchar(100),
@NewContent varchar(max),
@RegisterDate datetime,
@AdminId int,
@IsValid bit
 AS
 UPDATE News SET
 [Title] = @Title,[NewContent] = @NewContent,[RegisterDate] = @RegisterDate,[AdminId] = @AdminId,[IsValid] = @IsValid
 WHERE NewsId=@NewsId

GO
-- 删除
CREATE PROCEDURE dbo.UP_News_Delete
@NewsId int
 AS
 DELETE News
  WHERE NewsId=@NewsId

GO
--根据ＩＤ查看实体
CREATE PROCEDURE dbo.UP_News_GetModel
@NewsId int
 AS
 SELECT
 NewsId,Title,NewContent,RegisterDate,AdminId,IsValid
  FROM News
  WHERE NewsId=@NewsId

GO

---------------------
CREATE PROCEDURE dbo.UP_News_GetModelInfo

 AS
 SELECT
 NewsId,Title,NewContent,RegisterDate,AdminId,IsValid
  FROM News
 

GO

----------分页

-- select top 10 * from News where NewsId not in(
--  select top (2-1)*10 NewsId from News)
--  修改
 alter
-- 创建 
create proc dbo.UP_News_Page
(
-- 一页显示几条
@pageSize int,
-- 第几页
@curPage int,
@orderName nvarchar(250)
)
as
set nocount on
   declare @sql nvarchar(max)
   set @sql = 'select top ' + convert(nvarchar(250),@pageSize) + ' * from News'
   set @sql = @sql + ' where NewsId not in(select top ' + convert(nvarchar(250),(@curPage-1)*@pageSize)
   set @sql = @sql + ' NewsId from News ) '
   print @sql
   exec(@sql)
set nocount off
go

create proc dbo.UP_News_Page_Count
as
set nocount on

  select count(NewsId) from News
set nocount off
go