
存储过程:
CREATE procedure dbo.getid
@selectsql nvarchar(1000), /* query sql */
@curid int , /* current record id */
@top int = 0 /* select top ??? */
as
set nocount on
declare @previd int /* output */
declare @nextid int /* output */
declare @sql nvarchar(3000)
/*Deal with the SQL sentence spread into */
declare @tempsql nvarchar(1000)
declare @countsql nvarchar(1000)
declare @m int /* Position in SQL sentence of "from" */
declare @j int /* Position in SQL sentence of "order by" */
declare @n int /* Length of the SQL sentence */
declare @k int /* Total amount of records chosen in SQL sentence */
set @tempsql = @selectsql
set @tempsql = lower(@tempsql)
set @n = len(@tempsql)
set @m = charindex('from',@tempsql)
set @j = charindex('order',@tempsql)
set @n = @n - @m + 1
if @top > 0
set @tempsql = 'select top '+ cast(@top as varchar(10)) + ' id ' + substring(@tempsql,@m,@n)
else
set @tempsql = 'select id ' + substring(@tempsql,@m,@n)
/*Without keywords "order by"*/
if @j is null or @j = 0
set @countsql = 'select @k=count(id) ' + substring(@tempsql,@m,@n)
else
begin
set @n = @j - @m
set @countsql = 'select @k=count(id) ' + substring(@tempsql,@m,@n)
end
exec sp_executesql @countsql,N'@k int output',@k output
if @top > 0 and @k > @top
set @k = @top
/*Make SQL*/
set @sql = ''+Char(13)+
'declare @currow int'+Char(13)+
'declare @id int'+Char(13)+
'declare @i int'+Char(13)+
'declare cursor_getid scroll cursor'+Char(13)+
'for'+Char(13)+
''+@tempsql+''+Char(13)+
'open cursor_getid'+Char(13)+
'/* get the row of the current record */'+Char(13)+
'set @i = 0'+Char(13)+
'set @id = 0'+Char(13)+
'while (@id<>'+cast(@curid as varchar(100))+')'+Char(13)+
'begin'+Char(13)+
' fetch next from cursor_getid into @id'+Char(13)+
' set @i = @i + 1'+Char(13)+
' if @i > '+cast(@k as varchar(100))+''+Char(13)+
' break'+Char(13)+
'end'+Char(13)+
'if @i > '+cast(@k as varchar(100))+''+Char(13)+
'begin'+Char(13)+
' set @previd = 0'+Char(13)+
' set @nextid = 0'+Char(13)+
'end'+Char(13)+
'else'+Char(13)+
'begin'+Char(13)+
' set @currow = @i'+Char(13)+
' /* get the next record id */'+Char(13)+
' set @i = @currow + 1'+Char(13)+
' fetch absolute @i from cursor_getid into @nextid'+Char(13)+
' if @@fetch_status <> 0'+Char(13)+
' set @nextid = 0'+Char(13)+
' /* get the prev record id */'+Char(13)+
' set @i = @currow - 1'+Char(13)+
' fetch absolute @i from cursor_getid into @previd'+Char(13)+
' if @@fetch_status <> 0'+Char(13)+
' set @previd = 0'+Char(13)+
'end'+Char(13)+
'close cursor_getid'+Char(13)+
'deallocate cursor_getid'
exec sp_executesql @sql,N'@previd int output,@nextid int output',@previd output,@nextid output
select @previd as previd,@nextid as nextid
GO
<%
'调用存储过程,返回上一条记录的ID号和下一条记录的ID号
Sub GetID(objConn,sSQL,iCurId,byref iNext,byref iPrev)
' 参数sSQL应类似于“select [top n] id from table_name [where conditions] [order by fieldname]”,若有where,order by 关键字则需同索引(内容列表)页面选取内容的sSQL语句保持一致
' iCurId 为当前记录的ID号
Dim Temp,i,j
j = 0
Temp = LCase(Replace(sSQL," "," "))
If InStr(Temp,"select top") <> 0 Then
Temp = Split(Temp," ")
For i=0 To UBound(Temp)
If Temp(i) = "top" Then
Exit For
End If
Next
j = Cint(Temp(i+1))
End If
Dim objRs
If j > 0 Then
Set objRs = objConn.Execute("EXEC getid '"&replace(sSQL,"'","''")&"',"&iCurId&","&j)
Else
Set objRs = objConn.Execute("EXEC getid '"&replace(sSQL,"'","''")&"',"&iCurId)
End If
If objRs.eof Then
iNext = 0
iPrev = 0
Else
iNext = objRs("nextid")
iPrev = objRs("previd")
End If
Call CloseRecordSet(objRs)
End Sub
%>
'调用存储过程,返回上一条记录的ID号和下一条记录的ID号
Sub GetID(objConn,sSQL,iCurId,byref iNext,byref iPrev)
' 参数sSQL应类似于“select [top n] id from table_name [where conditions] [order by fieldname]”,若有where,order by 关键字则需同索引(内容列表)页面选取内容的sSQL语句保持一致
' iCurId 为当前记录的ID号
Dim Temp,i,j
j = 0
Temp = LCase(Replace(sSQL," "," "))
If InStr(Temp,"select top") <> 0 Then
Temp = Split(Temp," ")
For i=0 To UBound(Temp)
If Temp(i) = "top" Then
Exit For
End If
Next
j = Cint(Temp(i+1))
End If
Dim objRs
If j > 0 Then
Set objRs = objConn.Execute("EXEC getid '"&replace(sSQL,"'","''")&"',"&iCurId&","&j)
Else
Set objRs = objConn.Execute("EXEC getid '"&replace(sSQL,"'","''")&"',"&iCurId)
End If
If objRs.eof Then
iNext = 0
iPrev = 0
Else
iNext = objRs("nextid")
iPrev = objRs("previd")
End If
Call CloseRecordSet(objRs)
End Sub
%>