create table Book (
BID int identity,
BSID int null,
BName nvarchar(200) not null,
BAuthor nvarchar(200) not null,
BISBN nvarchar(50) not null,
BTOC ntext null,
BComment ntext null,
BPic nvarchar(100) null,
BPrice numeric(8,2) not null,
BCount int not null,
BDate datetime not null,
BSaleCount int null,
constraint PK_BOOK primary key (BID)
)
create table BSCategory (
BSID int identity,
BLID int null,
BLName varchar(30) not null,
constraint PK_BSCATEGORY primary key (BSID)
)
这两个表中的bsid是主外键关系 我的要求是要分页,请各位大神指点指点哈,具体是什么样的请告知!!!
BID int identity,
BSID int null,
BName nvarchar(200) not null,
BAuthor nvarchar(200) not null,
BISBN nvarchar(50) not null,
BTOC ntext null,
BComment ntext null,
BPic nvarchar(100) null,
BPrice numeric(8,2) not null,
BCount int not null,
BDate datetime not null,
BSaleCount int null,
constraint PK_BOOK primary key (BID)
)
create table BSCategory (
BSID int identity,
BLID int null,
BLName varchar(30) not null,
constraint PK_BSCATEGORY primary key (BSID)
)
这两个表中的bsid是主外键关系 我的要求是要分页,请各位大神指点指点哈,具体是什么样的请告知!!!
/****** Object: StoredProcedure [dbo].[GetListByPageIndex] Script Date: 03/27/2013 09:33:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO-- =============================================
-- Author: <Author,CodeRobot>
-- Create date: <Create Date,2011-04-26>
-- Description: <Description,通用分页存储过程2000版>
-- =============================================
ALTER PROCEDURE [dbo].[GetListByPageIndex]
(
@TableName varchar(50), --表名
@PrimaryKey varchar(50), --单一主键或唯一值键[排序列]
@Fields varchar(1000)='*', --返回的列(全部列用*)
@Order varchar(200), --排序字段(必须传值!支持多列排序,不用加order by)
@Where Varchar(500)=N'', --搜索条件(不用加where)
@PageSize int, --每页大小
@PageIndex int=1, --当前页
@TotalRowCount bigInt output --返回总行数
)
AS
declare @strSQL varchar(8000) -- 主语句
DECLARE @tempWhere1 VARCHAR(1000) -- 临时Where1
DECLARE @tempWhere2 VARCHAR(1000) -- 临时Where2
DECLARE @tempOrder1 VARCHAR(1000) -- 临时Order1
DECLARE @tempOrder2 VARCHAR(1000) -- 临时Order2 set @TotalRowCount = 0
if ISNULL(@Where,'') = ''
begin
set @tempWhere1=''
set @tempWhere2=' where '
end
else
begin
set @tempWhere1=' where '+ @Where
set @tempWhere2=' where ' + @where + ' and '
end if ISNULL(@Order,'')=''
begin
set @tempOrder1 = ' order by ' + @PrimaryKey
set @tempOrder2 = ' order by ' + @PrimaryKey + ' desc'
end
else
begin
set @tempOrder1 = ' order by ' + @Order
set @tempOrder2 = ' order by ' + REPLACE(REPLACE(@Order,'asc','desc'),'desc','asc')
end DECLARE @SqlCount NVARCHAR(4000)
DECLARE @TotalPageCount int
SET @SqlCount = 'SELECT @TotalRowCount=COUNT(*) FROM ' + @TableName + @tempWhere1
EXEC SP_EXECUTESQL @SqlCount,N'@TotalRowCount INT OUTPUT,@TotalPageCount INT OUTPUT',
@TotalRowCount OUTPUT,@TotalPageCount OUTPUT if @PageIndex > CEILING((@TotalRowCount+0.0)/@PageSize)
begin
SET @PageIndex = CEILING((@TotalRowCount+0.0)/@PageSize)
end if @PageIndex = 1 OR @PageIndex >= CEILING((@TotalRowCount+0.0)/@PageSize)
begin
if @PageIndex = 1 --如果是第一页就执行以上代码,这样会加快执行速度
begin
set @strSQL = 'select top ' + str(@PageSize) +' '+@Fields+ ' from [' + @TableName + ']' + @tempWhere1 + ' ' + @tempOrder1
end if @PageIndex >= CEILING((@TotalRowCount+0.0)/@PageSize) --返回最后一页数据,这样会加快执行速度
begin
set @strSQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Fields + ' FROM ('
+ 'SELECT TOP ' + STR(ABS(@TotalRowCount-@PageSize*@PageIndex+@PageSize))
+ ' ' + @Fields + ' FROM '
+ @TableName + @tempWhere1 + @tempOrder2 + ' ) AS tmpTab '
+ @tempOrder1
end
end
else
begin --以下代码赋予了@strSQL以真正执行的SQL代码
if @PageIndex <= CEILING((@TotalRowCount+0.0)/@PageSize)/2
begin
set @strSQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Fields + ' FROM ( '
+ 'SELECT TOP ' + STR(@PageSize) + ' ' + @Fields + ' FROM ( '
+ ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @Fields
+ ' FROM ' + @TableName + @tempWhere1 + @tempOrder1 + ' ) AS tmpTab '
+ @tempOrder2 + ' ) AS tmpTab ' + @tempOrder1
end
else --反向检索
begin
set @strSQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Fields + ' FROM ( '
+ ' SELECT TOP ' + STR(abs(@TotalRowCount-@PageSize *@PageIndex+@PageSize)) + ' ' + @Fields
+ ' FROM ' + @TableName + @tempWhere1 + @tempOrder2 + ' ) AS tmpTab '
+ @tempOrder1
end
end
EXEC(@strSQL)