help with table variable and cursor - stored proc never ends
-
I have a stored proc that is the datasource for a reporting services report.
Unfortunately, when I run the sp, it runs continuously and I can't figure out why.
Here is the stored proc:
/******************************************************************************
CREATE Procedure rpt_BidSummary
@SelectednLogSaleUID int
AS
/* create table variable */
DECLARE @BidSummaryTable TABLE
(
nLogSaleUID int,
cLogSaleName varchar(200),
cSpeciesName varchar(100),
cSortName varchar(50),
nOrder int,
cCustomerName varchar(200),
cDestinationName varchar(200),
dBidMBF bit,
mTLC money,
grossMBF money,
netMBF money,
Winner int,
Rank int
)
/* insert bids into table variable */
INSERT INTO @BidSummaryTable
SELECT DISTINCT
TOP 100 PERCENT LS.nLogSaleUID, LS.cLogSaleName, Sp.cSpeciesName, So.cSortName, So.nOrder, C.cCustomerName, D.cDestinationName,
BSD.dBidMBF, BSD.mTLC, ISNULL(BSD.dBidMBF, 0) * (CASE WHEN (BSenD.bwinner = 1 AND BSen.bWinner = 1)
THEN BSenD.dNewMBF ELSE LSSD.dVolMBF END) AS grossMBF, ISNULL(BSD.dBidMBF, 0) * (CASE WHEN (BSenD.bwinner = 1 AND
BSen.bWinner = 1) THEN BSenD.dNewMBF ELSE LSSD.dVolMBF END) - ISNULL(BSD.mTLC, 0) AS netMBF,
MAX((CASE WHEN (BSenD.bwinner = 1 AND BSen.bWinner = 1) THEN 1 ELSE 0 END)) AS Winner, 0 AS Rank
FROM dbo.BidScenarioDetail BSenD INNER JOIN
dbo.BidScenario BSen ON BSenD.nBidScenarioUID = BSen.nBidScenarioUID RIGHT OUTER JOIN
PBCommon.dbo.Sort So INNER JOIN
dbo.BidSpecies BS INNER JOIN
dbo.LogSaleSpeciesDetail LSSD INNER JOIN
dbo.LogSaleSpecies LSS ON LSSD.nLogSaleSpeciesUID = LSS.nLogSaleSpeciesUID AND LSSD.nLogSaleSpeciesUID = LSS.nLogSaleSpeciesUID AND
LSSD.nLogSaleSpeciesUID = LSS.nLogSaleSpeciesUID AND LSSD.nLogSaleSpeciesUID = LSS.nLogSaleSpeciesUID AND
LSSD.nLogSaleSpeciesUID = LSS.nLogSaleSpeciesUID INNER JOIN
dbo.LogSale LS INNER JOIN
dbo.Bids B ON LS.nLogSaleUID = B.nLogSaleUID ON LSS.nLogSaleUid = LS.nLogSaleUID INNER JOIN
dbo.BidSpeciesDetail BSD ON LSSD.nLogSaleSpeciesDetailUID = BSD.nLogSaleSpeciesDetailUID ON BS.nBidUID = B.nBidUID AND
BS.nBidSpeciesUID = BSD.nBidSpeciesUID INNER JOIN
PBCommon.dbo.Species Sp ON Sp.nSpeciesUID = BS.nSpeciesUID ON So.nSortUID = BSD.nSortUID LEFT OUTER JOIN
PBCommon.dbo.Customer C ON B.nCustomerUID = C.nCustomerUID LEFT OUTER JOIN
PBCommon.dbo.Destination D ON B.nDestinationUID = D.nDestinationUID ON BSenD.nBidSpeciesDetailUID = BSD.nBidSpeciesDetailUID
GROUP BY LS.nLogSaleUID, LS.cLogSaleName, Sp.cSpeciesName, So.cSortName, So.nOrder, C.cCustomerName, D.cDestinationName, BSD.dBidMBF,
BSD.mTLC, ISNULL(BSD.dBidMBF, 0) * (CASE WHEN (BSenD.bwinner = 1 AND BSen.bWinner = 1) THEN BSenD.dNewMBF ELSE LSSD.dVolMBF END),
ISNULL(BSD.dBidMBF, 0) * (CASE WHEN (BSenD.bwinner = 1 AND BSen.bWinner = 1) THEN BSenD.dNewMBF ELSE LSSD.dVolMBF END)
- ISNULL(BSD.mTLC, 0)
HAVING (LS.nLogSaleUID = 12) AND (BSD.dBidMBF > 0)
ORDER BY LS.cLogSaleName, Sp.cSpeciesName, So.nOrder, MAX((CASE WHEN (BSenD.bwinner = 1 AND BSen.bWinner = 1) THEN 1 ELSE 0 END)) DESC,
ISNULL(BSD.dBidMBF, 0) * (CASE WHEN (BSenD.bwinner = 1 AND BSen.bWinner = 1) THEN BSenD.dNewMBF ELSE LSSD.dVolMBF END)
- ISNULL(BSD.mTLC, 0) DESC
/* declare and set rank variable */
Declare @myRank int
set @myRank=1
/* declare sortorder variable for looping thru species sorts */
Declare @mySortorder int
/* create field variables */
Declare @nLogSaleUID int
Declare @cLogSaleName varchar(200)
Declare @cSpeciesName varchar(100)
Declare @cSortName varchar(50)
Declare @nOrder int
Declare @cCustomerName varchar(200)
Declare @cDestinationName varchar(200)
Declare @dBidMBF bit
Declare @mTLC money
Declare @grossMBF money
Declare @netMBF money
Declare @Winner int
Declare @Rank int
/* declare and open cursor for @BidSummaryTable */
Declare myCursor cursor
Local
For
Select * from @BidSummaryTable
For update of Rank
Open myCursor
/* fetch the first row */
Fetch From myCursor into
@nLogSaleUID,
@cLogSaleName,
@cSpeciesName,
@cSortName,
@nOrder,
@cCustomerName,
@cDestinationName,
@dBidMBF,
@mTLC,
@grossMBF,
@netMBF,
@Winner,
@Rank
/* get the value of the first sortorder and assign to temp variable */
set @mySortorder = @nOrder
While @@Fetch_Status =0 /* keep fetching rows until their aren't anymore to fetch */
BEGIN
/* update the rank per sort */
While @mySortorder = @nOrder and @@Fetch_Status =0
BEGIN
Update @BidSummaryTable
set rank= @myRank /* update the rank */
where current of myCursor
set @myRank=@myRank+1 /* add one to the rank */
/* fetch the next row and insert into the cursor */
Fetch Next from myCursor into
@nLogSaleUID,
@cLogSaleName,
@cSpeciesName,
@cSortName,
@nOrder,
@cCustomerName,
@cDestinationName,
@dBidMBF,
@mTLC,
@grossMBF,
@netMBF,
@Winner,
@Rank
END
set @mySortorder = @mySortorder + 1 /* add one to the sortorder */
set @myRank=1 /* reset the rank variable for the next sort */
End
/* close and deallocate cursor */
close myCursor
Deallocate myCursor
Select * from @BidSummaryTable
GO
/******************************************************************************
I can run to the line: While @@Fetch_Status =0 and then select @BidSummaryTable and get results so I know the table variable is working. It seems like something is wrong with the cursor, but I have another stored proc with the same structure that works great.I know that cursors are frowned upon these days, but if anyone has any hints or suggestions, my ears/eyes are open.Also, if the working stored proc or a copy of the db would be helpful, just let me know.Thanks, Megan -
>>While @@Fetch_Status =0 /* keep fetching rows until their aren't anymore to fetch */
You aren't doing anything inside the loop to change @@Fetch_Status. Therefore infinite loop. Need a Fetch within the loop.
There's also a pretty good chance you can do this set-based and drop the cursor completely.
-
What about this statement?
/* fetch the next row and insert into the cursor */
Fetch Next from myCursor into
@nLogSaleUID,
@cLogSaleName,
@cSpeciesName,
@cSortName,
@nOrder,
@cCustomerName,
@cDestinationName,
@dBidMBF,
@mTLC,
@grossMBF,
@netMBF,
@Winner,
@Rank
I'll look into that further, thanks for the pointer.
Do you have any suggestions for doing this set-based?
Thanks again, Megan
-
Sorry, let me rephrase that:
>>While @mySortorder = @nOrder and @@Fetch_Status =0
You have an outer loop based on @@Fetch_Status =0 but your Fetch is inside another conditional statement.
What happens if @mySortorder does not equal @nOrder while @@Fetch_Status is still zero ? You have an infinite loop then, because the inner loop won't execute, but the Fetch is inside the inner loop.
As for set-based ... what exactly are you trying to do with this data ?
-
Basically, I just want to "rank" or give a row number per sort.
I rank into some info on rank and ended up removing all of the cursor stuff and replacing with:
/* declare and set start variable for ranking */
declare @Start int
select @Start = 0
/* declare order id variable */
declare @Order int
/* create temp table to hold current available order ids */
select distinct nOrder into #BSDtemp from @BidSummaryTable
/* update rank field with rank based on sortorder */
update t1 set @Start = Rank = case when t1.nOrder = @Order then @Start + 1 else 1 end,@Order = t2.nOrder
from @BidSummaryTable t1 join #BSDtemp t2 on t1.nOrder = t2.nOrder
/* drop temp table */
drop table #BSDtemp
Which is just a million times slicker.
Thanks for trying to help. Serves me right for trying to use cursors.
-megan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply