CREATE PROCEDURE dbo.GetPageRecord ( @tblName varchar(255), -- 表名 @fldlist varchar(1000)='*', @fldName varchar(255), --排序字段 @KeyField varchar(255), --主键 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsCount bit = 0, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 1, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(4000) = '' -- 查询条件 (注意: 不要加 where) ) AS SET NOCOUNT ON DECLARE @PageLowerBound int DECLARE @PageUpperBound int
-- Set the page bounds SET @PageLowerBound = @PageSize * @PageIndex SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results create table #temp ( RecNo int IDENTITY (1, 1) NOT NULL, oldid int ) --generate record Declare @Sqlstr varchar(6000) set @sqlstr='select '+@Keyfield+' from '+@tblname if(@strWhere<>'') begin set @Sqlstr=@sqlstr+' where ('+@strWhere+')' end set @sqlstr=@sqlstr+' order by '+@fldName if(@ordertype=0) begin set @sqlstr=@sqlstr+' asc' end else begin set @sqlstr=@sqlstr+' desc' end set @sqlstr='insert into #temp (oldid) '+@sqlstr execute(@sqlstr) set @sqlstr='SELECT '+@fldList+' FROM '+@tblname+' TableA (nolock), #temp T WHERE T.oldid = TableA.'+@keyfield+' AND T.RecNo > '+ cast(@PageLowerBound as varchar)+' AND T.RecNo < '+cast(@PageUpperBound as varchar)+' ORDER BY T.RecNo' execute(@sqlstr) GO
·详情请访问:http://www.515dns.com
·详情请访问:http://www.515dns.com
2 使用存储过程.这个速度快些.因为相比SQL他先进行了编译.网上代码实例很多,可以看看.我建议看 阿良写的 我觉得还不错 而且他是为了技术研究 上面有很多东西都开源
www.chenjingliang.com
0001 123456
CREATE PROCEDURE dbo.GetPageRecord
(
@tblName varchar(255), -- 表名
@fldlist varchar(1000)='*',
@fldName varchar(255), --排序字段
@KeyField varchar(255), --主键
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 1, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(4000) = '' -- 查询条件 (注意: 不要加 where)
)
AS
SET NOCOUNT ON
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
create table #temp
(
RecNo int IDENTITY (1, 1) NOT NULL,
oldid int
)
--generate record
Declare @Sqlstr varchar(6000)
set @sqlstr='select '+@Keyfield+' from '+@tblname
if(@strWhere<>'')
begin
set @Sqlstr=@sqlstr+' where ('+@strWhere+')'
end
set @sqlstr=@sqlstr+' order by '+@fldName
if(@ordertype=0)
begin
set @sqlstr=@sqlstr+' asc'
end
else
begin
set @sqlstr=@sqlstr+' desc'
end
set @sqlstr='insert into #temp (oldid) '+@sqlstr
execute(@sqlstr)
set @sqlstr='SELECT '+@fldList+' FROM '+@tblname+' TableA (nolock), #temp T WHERE T.oldid = TableA.'+@keyfield+' AND T.RecNo > '+ cast(@PageLowerBound as varchar)+' AND T.RecNo < '+cast(@PageUpperBound as varchar)+' ORDER BY T.RecNo'
execute(@sqlstr)
GO