Classic ASP调用存储过程rs.EOF始终为true，无数据返回求助

我在Classic ASP脚本中执行名为NewReturnReports的存储过程，手动在数据库指定日期范围执行该存储过程能返回492行数据，但脚本执行后页面始终显示“No record there”，即rs.EOF始终为true，求排查原因。

Classic ASP脚本代码 #

set conn = server.CreateObject ("ADODB.Connection")
conn.Open (<---QueryString Here--->)
conn.CommandTimeout = 50000
set rs = server.CreateObject("ADODB.Recordset")
DIM cmd
SET cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandType = 4
cmd.CommandText = "NewReturnReports"
set param1 = cmd.CreateParameter("@fromdate", adDate , adParamInput)
param1.Value = "2023-02-01"
cmd.Parameters.Append param1

set param2 = cmd.CreateParameter("@todate", adDate , adParamInput)
param2.Value = "2023-03-13"
cmd.Parameters.Append param2


rs = cmd.Execute
    if rs.EOF = True then
        Response.Write "No record there"
    else
%>  
    <script language="javascript">
function winpopup(pagename)
{
window.open(pagename,'cc','top=150,left=150,width=250,height=260,scrollbars=yes,locationbar=yes,menubar=no,status=no');
}</script>

存储过程代码 #

ALTER PROCEDURE [dbo].[NewReturnReports]    
    @fromdate DATE,     
    @todate   DATE     
AS
    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF
BEGIN
    IF OBJECT_ID('tempdb..#tmp_ReturnReports') IS NOT NULL 
        DROP TABLE #tmp_ReturnReports

    SELECT 
        dbo.[Returns].OrderID, dbo.[Returns].ProductId,   
        dbo.[Returns].DayDuration, dbo.[Returns].Status, 
        dbo.[Returns].OrderDate, dbo.[Returns].ReturnDate, 
        dbo.orderitems.CostPrice,
        CASE 
            WHEN (NOT dbo.OnlineReturn.returnReason = '' AND NOT dbo.OnlineReturn.returnReason IS NULL AND LEN(dbo.OnlineReturn.returnReason) > 5) 
                THEN dbo.OnlineReturn.returnReason 
            ELSE dbo.[Returns].Reason 
        END AS reason, 
        MONTH(dbo.[Returns].CrDate) AS Month, 
        DAY(dbo.[Returns].CrDate) AS Day, 
        YEAR(dbo.[Returns].CrDate) AS Year,
        dbo.[Returns].Comments AS OComments, 
        dbo.orderitems.UnitPrice, 
        dbo.Orders.BillTo, dbo.Orders.BillToPhone, 
        dbo.Shipping.ShipDate, dbo.[Returns].StockStatus, 
        dbo.[Returns].StockDetails,
        dbo.Orders.ShopperEMail, dbo.[Returns].CrDate, 
        dbo.[Returns].ItemId, dbo.OnlineReturn.Comment AS Comments, 
        dbo.orderitems.rowid - 116611 AS Specid 
    INTO
        #tmp_ReturnReports
    FROM 
        dbo.orderitems 
    INNER JOIN 
        dbo.[Returns] ON dbo.orderitems.OrderID = dbo.[Returns].OrderID 
                      AND dbo.orderitems.ProductID = dbo.[Returns].ProductId 
                      AND dbo.orderitems.rowid =[Returns].ItemId 
    INNER JOIN 
        dbo.Orders ON dbo.orderitems.OrderID = dbo.Orders.OrderID 
    INNER JOIN 
        dbo.Shipping ON dbo.Orders.OrderID = dbo.Shipping.OrderID 
    LEFT OUTER JOIN 
        dbo.OnlineReturn ON dbo.orderitems.rowid = dbo.OnlineReturn.RowId
    WHERE 
        CAST(dbo.[Returns].CrDate AS date) >= CAST(@fromdate AS date) 
        AND CAST(dbo.[Returns].CrDate AS date) <= CAST(@todate AS date)
    GROUP BY 
        dbo.[Returns].OrderID, dbo.[Returns].ProductId, 
        dbo.[Returns].DayDuration, dbo.[Returns].Status, 
        dbo.[Returns].OrderDate, dbo.[Returns].ReturnDate, 
        dbo.orderitems.CostPrice, MONTH(dbo.[Returns].CrDate),
        DAY(dbo.[Returns].CrDate), YEAR(dbo.[Returns].CrDate),  
        dbo.[Returns].Comments, dbo.orderitems.UnitPrice, 
        dbo.Orders.BillTo, dbo.Orders.BillToPhone, 
        dbo.Shipping.ShipDate, dbo.[Returns].StockStatus,
        dbo.[Returns].StockDetails, dbo.Orders.ShopperEMail, 
        dbo.[Returns].CrDate, dbo.[Returns].ItemId, 
        dbo.OnlineReturn.Comment, 
        CASE 
            WHEN (NOT dbo.OnlineReturn.returnReason = '' AND
NOT dbo.OnlineReturn.returnReason IS NULL AND LEN(dbo.OnlineReturn.returnReason) > 5) 
                THEN dbo.OnlineReturn.returnReason 
            ELSE dbo.[Returns].Reason 
        END, dbo.orderitems.rowid - 116611

    SELECT 
        trr.OrderID, trr.ProductId, trr.DayDuration, trr.Status, 
        trr.OrderDate, trr.ReturnDate, trr.CostPrice, trr.reason, 
        trr.Month, trr.Day, trr.Year, trr.UnitPrice, trr.BillTo,
        trr.ShipDate, trr.BillToPhone, trr.StockStatus, 
        trr.StockDetails, trr.ShopperEMail, 
        COUNT(DISTINCT dbo.VwIdentifyReturnCustomers.OrderID) AS RetOrderCount, 
        COUNT(dbo.VwIdentifyReturnCustomers.ProductId) AS RetProdCount, 
        COUNT(DISTINCT dbo.VwIdentifyReturnCustomers.rowid) AS RetIndItemCount, 
        MAX(trr.CrDate) AS crdate, 
        dbo.V_PatternedBy.PatternMaker AS Createdby, 
        dbo.CustomerSizeStyleOption.sizeoption,  
        dbo.CustomerSizeStyleOption.styleoption,
        eshakti.dbo.Product.StyleType, 
        dbo.OnlineReturn.Comment AS Comments, 
        dbo.V_PatternedBy.Name
    FROM 
        #tmp_ReturnReports trr 
    INNER JOIN 
        dbo.VwIdentifyReturnCustomers ON trr.ShopperEMail = dbo.VwIdentifyReturnCustomers.ShopperEMail 
    INNER JOIN 
        eshakti.dbo.Product ON trr.ProductId = eshakti.dbo.Product.ProductId 
    LEFT OUTER JOIN 
        dbo.OnlineReturn ON trr.ItemId = dbo.OnlineReturn.RowId 
    LEFT OUTER JOIN 
        dbo.CustomerSizeStyleOption ON trr.ItemId = dbo.CustomerSizeStyleOption.rowid 
    LEFT OUTER JOIN 
        dbo.V_PatternedBy ON trr.ItemId = dbo.V_PatternedBy.Rowid
    GROUP BY 
        trr.OrderID, trr.ProductId, trr.DayDuration, trr.Status, 
        trr.OrderDate, trr.ReturnDate, trr.CostPrice, trr.reason, 
        trr.Month, trr.Day, trr.Year, trr.UnitPrice, trr.BillTo,
        trr.ShipDate, trr.BillToPhone, trr.StockStatus, trr.StockDetails, 
        trr.ShopperEMail, dbo.V_PatternedBy.PatternMaker,
        dbo.CustomerSizeStyleOption.sizeoption, 
        dbo.CustomerSizeStyleOption.styleoption, 
        eshakti.dbo.Product.StyleType, dbo.OnlineReturn.Comment, 
        dbo.V_PatternedBy.Name

    DROP TABLE #tmp_ReturnReports
END

可能的原因及排查步骤 #

• Recordset赋值错误：这是最可能的核心问题。Classic ASP中，ADODB.Command.Execute返回的Recordset对象必须用Set关键字赋值，脚本中rs = cmd.Execute写法错误，正确代码应为：

  Set rs = cmd.Execute
  

  没有Set会导致rs被赋值为非Recordset类型的值，后续rs.EOF判断自然出错。
• 参数类型转换问题：脚本中给adDate类型的参数赋值字符串"2023-02-01"，可能因服务器区域设置或ADODB解析问题导致参数传递错误。建议转换为ASP Date类型后赋值：

  param1.Value = CDate("2023-02-01")
  param2.Value = CDate("2023-03-13")
  

• 连接环境差异：检查ASP使用的数据库连接字符串对应的用户权限、连接的数据库实例（如测试/生产库）是否和手动执行时一致，避免因权限不足或库实例不同导致无数据返回。
• 存储过程的NOCOUNT设置：虽然SET NOCOUNT ON通常不影响结果集返回，但可尝试注释该语句后测试，排除其干扰。
• 日期格式兼容性：若服务器区域设置为非ISO日期格式（如MM/DD/YYYY），字符串"2023-02-01"可能被错误解析，使用CDate转换可规避此问题。

内容的提问来源于stack exchange，提问作者Mahendra Kumar