SQL Server rs.eof always returns true. Stored procedure is not returning values in Classic ASP

I am trying to execute this stored procedure in a Classic ASP script:

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>

rs.EOF is always true as I get the response string "No record there" on the screen.

My stored procedure looks like this

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

I manually ran the procedure in the given date range, it returns 492 rows.

What could be the reason that stored procedure is not returning values in the script? Any help is appreciated

Not really an answer (so community wiki), but whenever you have code like this, as we see in the question:

SELECT <columns>
 INTO #TemptTable
FROM t1
INNER JOIN t2 ..
...

SELECT <columns>
FROM #TempTable
INNER JOIN t3
...

You can always change it to run as a single query, without the temp table, like this:

SELECT <columns>
FROM
(
   -- First query (without the INTO clause) goes here
   SELECT <columns>
   FROM t1
   INNER JOIN t2 ..
   ...
) t0
INNER JOIN t3 
...

And you will generally get MUCH better performance this way.

There are times where the temp table helps avoid pathological execution plans, but those are the exception, not the rule.

This:

WHERE 
    CAST(dbo.[Returns].CrDate AS date) >= CAST(@fromdate AS date) 
    AND CAST(dbo.[Returns].CrDate AS date) <= CAST(@todate AS date)

Can reduce to this:

WHERE dbo.[Returns].CrDate >= @fromdate 
    AND dbo.[Returns].CrDate < DATEADD(day, 1, @todate)

And it will again run MUCH faster. The original prevented any index use on the CrDate column (it was not sargable), as well as forcing a conversion operation for this column on every row in the table... even those you don't need. This fixes that.

Depending on the values in the column, you might be able to reduce this:

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

down to this:

COALESCE(NULLIF(RTRIM(dbo.OnlineReturn.returnReason), '')), dbo.[Returns].Reason)

Note this is missing the check on LEN() > 5 , but adds a check to ensure it's not just white space.

SQL Server rs.eof always returns true. Stored procedure is not returning values in Classic ASP

1条答案

相关问题

热门标签

最新问答