一个分页存储过程的两难抉择

[solotarn]

现在用存储过程实现一个分页过程。要求如下(asp,ms sql)：
1、要对得到的记录集遍历两遍。
2、要得到总的记录数。
3、要用到动态SQL。如包含关键字的搜索。

现在似乎无法实现以上的目标。

由于用到动态SQL，包含有单引号，因此似乎只能用COMMAND对象的形式.

由于要对得到的记录集遍历两遍，因此不能通过' set recordset = command.execute'的形式得到记录集，因为此种方法得到的记录集的cursorType只能向前,而不能向后移动。因此只能如下：

recordset.source = command
recordset.cursortype = 2 或 3
recordset.open
的形式得到支持向后移动的记录集。

但这种形式得到的recordcount却只能是真正返回的记录数，而不是总的记录数。
用OUTPUT parameter或直接 return 的形式都无法得到总记录树，不知为什么，望哪位大虾指点。

分页存储过程
===========================================
use dbshop
/*Listing 4: SELECT_WITH_PAGING Stored Procedure*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUBQUERY_PAGING]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SUBQUERY_PAGING]
GO
CREATE PROCEDURE SUBQUERY_PAGING (
@strFields varchar(4000),
@strPK varchar(100),
@strTable varchar(100),--for pk select
@strTables varchar(4000),
@intPageNo int = 1,
@intPageSize int = NULL,
@blnGetRecordCount bit = 0,
@strFilter varchar(8000) = NULL,
@strSort varchar(8000) = NULL,
@intRecordCount int OUTPUT,
@strGroup varchar(8000) = NULL)

/* Executes a SELECT statement that the parameters define,and returns a particular page of data (or all
rows) efficiently. */
AS
DECLARE @blnBringAllRecords bit
DECLARE @strPageNo varchar(50)
DECLARE @strPageSize varchar(50)
DECLARE @strSkippedRows varchar(50)
DECLARE @strFilterCriteria varchar(8000)
DECLARE @strSimpleFilter varchar(8000)
DECLARE @strSortCriteria varchar(8000)
DECLARE @strGroupCriteria varchar(8000)
/*DECLARE @intRecordcount int
DECLARE @intPagecount int
*/
/* Normalize the paging criteria.
If no meaningful inputs are provided, we can avoid paging and execute a more efficient query, so we will
set a flag that will help avoid paging (blnBringAllRecords). */
IF @intPageNo < 1
SET @intPageNo = 1
SET @strPageNo = CONVERT(varchar(50), @intPageNo)
IF @intPageSize IS NULL OR @intPageSize < 1 -- Bring all records, don't do paging.
SET @blnBringAllRecords = 1
ELSE
BEGIN
SET @blnBringAllRecords = 0
SET @strPageSize = CONVERT(varchar(50), @intPageSize)
SET @strPageNo = CONVERT(varchar(50), @intPageNo)
SET @strSkippedRows = CONVERT(varchar(50), @intPageSize * (@intPageNo - 1))
END

/* Normalize the filter and sorting criteria.
If the criteria are empty, we will avoid filtering and sorting, respectively, by executing more efficient
queries. */
IF @strFilter IS NOT NULL AND @strFilter != ''
BEGIN
SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
SET @strSimpleFilter = ' AND ' + @strFilter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilterCriteria = ''
END
IF @strSort IS NOT NULL AND @strSort != ''
SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
ELSE
SET @strSortCriteria = ''
IF @strGroup IS NOT NULL AND @strGroup != ''
SET @strGroupCriteria = ' GROUP BY ' + @strGroup + ' '
ELSE
SET @strGroupCriteria = ''

/* Now start doing the real work. */
IF @blnBringAllRecords = 1 -- Ignore paging and run a simple SELECT.
BEGIN

EXEC (
'SELECT ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria +
@strGroupCriteria + @strSortCriteria
)

END -- We had to bring all records.
ELSE -- Bring only a particular page.
BEGIN
IF @intPageNo = 1 -- In this case we can execute a more efficient
-- query with no subqueries.
EXEC (
'SELECT TOP ' + @strPageSize + ' ' + @strFields + ' FROM ' + @strTables +
@strFilterCriteria + @strGroupCriteria + @strSortCriteria
)
ELSE -- Execute a structure of subqueries that brings the correct page.
EXEC (
'SELECT ' + @strFields + ' FROM ' + @strTables + ' WHERE ' + @strPK + ' IN ' + '
(SELECT TOP ' + @strPageSize + ' ' + @strPK + ' FROM ' + @strTable +
' WHERE ' + @strPK + ' NOT IN ' + '(SELECT TOP ' + @strSkippedRows + ' ' + @strPK + ' FROM ' + @strTable +
@strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
@strSimpleFilter +
@strGroupCriteria +
@strSortCriteria + ') ' +
@strGroupCriteria +
@strSortCriteria
)
END -- We had to bring a particular page.

/* If we need to return the recordcount: */
IF @blnGetRecordCount = 1
IF @strGroupCriteria != ''
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' +
@strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)'
)
ELSE
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables + @strFilterCriteria +
@strGroupCriteria
)
/* get the recordCount in another effective way*/
select @intRecordCount=(select distinct rows from sysindexes where id=OBJECT_ID(@strTable) and indid<>255)


GO




asp code(jscript)
===================================
cmd = Server.CreateObject("ADODB.Command");
with (cmd) {
CommandType = adCmdSPStoredProc;
ActiveConnection = conn;
CommandText = spPaging;
Parameters.Append( CreateParameter("@strFeilds",adVarChar,adParaminput,4000,strFields));
Parameters.Append (CreateParameter("@strPK", adVarChar,adParaminput,100,"Product.ID"));
Parameters.Append (CreateParameter("@strTable", adVarChar,adParaminput,100,strTable));
Parameters.Append (CreateParameter("@strTables", adVarChar,adParaminput,4000,strTables));
Parameters.Append (CreateParameter("@intPageNo", adInteger,adParaminput,4,itemPage));
Parameters.Append (CreateParameter("@intPageSize", adInteger,adParaminput,4,20));
Parameters.Append (CreateParameter("@blnGetRecordCount", adBoolean,adParaminput,1,1));
Parameters.Append (CreateParameter("@strFilter", adVarChar,adParaminput,8000,strFilter));
Parameters.Append (CreateParameter("@strSort", adVarChar,adParaminput,8000,strSort));
Parameters.Append (CreateParameter("@intRecordCount", adInteger,adParamOutput));
}
rs.CursorLocation = 3;
rs.CursorType = 3;
rs.Source = cmd;
rs.LockType = 1;
rs.open();
recordCount =cmd.Parameters("@intRecordCount").Value;
//or recordCount = cmd.Parameters("RETURN_VALUE").Value;
//or recordCount = rs.RecordCount
//all these three don't work

[新鲜鱼排]

up

[solotarn]

还有那位高手指点一下存储过程中的下面两句分别是什么意思。
1、 EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
@strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)
)
2、 EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM' + @strTables + @strFilterCriteria +
@strGroupCriteria
)
谢谢了