对存储过程中的结果如何分页

[zywhao]

我在存储过程中，通过拼接字符串得到了一段sql语句
set @strSql='select cc.*,tbPortAutoComplte.PortName_chs from (select top 100 UserID,Name,CnName,SOG,InPort from VWShipInfo where ShipType=1 and (DATEDIFF(s, ReceiveTime, GETDATE()) <= 12*60*60) ) as cc LEFT OUTER JOIN dbo.tbPortAutoComplte ON cc.InPort = dbo.tbPortAutoComplte.ID '

EXEC Sp_executesql @strsql

执行结果为100条记录， 我想再增加一个分页，传入参数 @startRecordIndex 和@pageSize 直接在字符串中就将分页功能实现。 如传入参数 @startRecordIndex=20 @pagesize=10 从第20条记录开始显示10条， 查询结果就为需要的10条记录。。



谢谢高手们解答一下

[流逝]

取第20条记录开始显示10条记录的 语句 语句类似于：
select top 10 * from (select top 30 * from zhiwu_table order by id) as dd order by id desc

[-晴天]

set @strSql='select top('+ @pagesize+') cc.*,tbPortAutoComplte.PortName_chs from (select top 100 UserID,Name,CnName,SOG,InPort from VWShipInfo where ShipType=1 and (DATEDIFF(s, ReceiveTime, GETDATE()) <= 12*60*60) ) as cc LEFT OUTER JOIN dbo.tbPortAutoComplte ON cc.InPort = dbo.tbPortAutoComplte.ID where dbo.tbPortAutoComplte.ID>'+@startRecordIndex

  EXEC Sp_executesql @strsql

[以学习为目的]

declare @startRecordIndex  int,
        @pagesize    int
set     @startRecordIndex=20
set     @pagesize=10 

set @strSql='select top('+@pagesize+')cc.*,tbPortAutoComplte.PortName_chs from (select top 100 UserID,Name,CnName,SOG,InPort from VWShipInfo where ShipType=1 and (DATEDIFF(s, ReceiveTime, GETDATE()) <= 12*60*60) ) as cc LEFT OUTER JOIN dbo.tbPortAutoComplte ON cc.InPort = dbo.tbPortAutoComplte.ID where dbo.tbPortAutoComplte.ID>'+@startRecordIndex 

EXEC Sp_executesql @strsql

[luochaowei1987]

set @strSql='select cc.*,tbPortAutoComplte.PortName_chs from (select top 100 UserID,Name,CnName,SOG,InPort from VWShipInfo where ShipType=1 and (DATEDIFF(s, ReceiveTime, GETDATE()) <= 12*60*60) ) as cc LEFT OUTER JOIN dbo.tbPortAutoComplte ON cc.InPort = dbo.tbPortAutoComplte.ID ';
with t(c1,c2) as 
(
    EXEC Sp_executesql @strsql
)
select top @pagesize * from t where c1 not int(select top (@startRecordIndex-1) c1 from t order by c1)order by c1

[zywhao]

[Quote=引用 2 楼 的回复:]

SQL code
set @strSql='select top('+ @pagesize+') cc.*,tbPortAutoComplte.PortName_chs from (select top 100 UserID,Name,CnName,SOG,InPort from VWShipInfo where ShipType=1 and (DATEDIFF(s, ReceiveTime,……
[/Quote]



dbo.tbPortAutoComplte.ID>'+@startRecordIndex 其中的startRecordIndex 不是记录的ID 是记录数， 例如我已经显示到19条了， 这次从第20条记录显示。。 我觉这个语句还是有些问题呢