Its me again with another cursor (more difficult i think, at least for me 🙂 ) which i need to handle it to set-based, for any help, code suggestion I'll will be grateful. This is new area for me and i'm trying to learn as much as possible, seems to be hard to understand how to rewrite, you need to know how thing works.
i'm not able to provide testing data because is hard to reproduce, since lot of tables, views and joins come in play. I know that is hard to test without data, but previous examples i got here on this thread was just excellent, exactly what i need with great explanation.
I spend my whole day today working on this to make it work but without success. So here is the code:
ALTER FUNCTION [dbo].[fn_GetSumOfPointsForBid]
(
@bid_Id uniqueidentifier
)
RETURNS decimal (8, 2)
AS
BEGIN
DECLARE @bidPoints decimal(8,2);
DECLARE @criteriaID uniqueidentifier;
DECLARE @isDirect bit;
DECLARE @cftID uniqueidentifier;
SELECT @cftID = call_for_tender_ID FROM BIDS WHERE ID = @bid_Id;
SELECT @isDirect = IsDirectAuction FROM vw_CallForTender WHERE ID = @cftID;
IF(@isDirect = 0)
BEGIN
DECLARE my_cursor CURSOR FOR
SELECT CRITERIA_ID
FROM [dbo].[vw_Bid_Criterias]
WHERE BID_ID = @bid_Id
--New section for OtherCriteria
union
select drtc.CRITERIA_ID from BID_CALCULATIONS bc
inner join CRITERIAS c on bc.criteria_ID=c.ID
inner join BIDS b on b.ID=bc.bid_ID
inner join CALL_FOR_TENDERS cft on cft.id=b.call_for_tender_ID
inner join DECISIONS_ON_REQUESTS_FOR_TENDERS_CRITERIAS drtc on drtc.decision_for_tender_id=cft.decision_on_request_for_tender_ID and drtc.criteria_id=c.ID
where bid_ID=@bid_Id and drtc.points>0 and number>13
--END --New section for OtherCriteria
SET @bidPoints = 0.00;
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @criteriaID
IF(@@FETCH_STATUS = 0)
BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @valueTmp decimal(8,2);
SELECT @valueTmp = Calc_Points FROM [dbo].[vw_Bid_Criterias] WHERE CRITERIA_ID = @criteriaID and BID_ID = @bid_Id;
SET @bidPoints = @bidPoints + @valueTmp;
FETCH NEXT FROM my_cursor
INTO @criteriaID
END
END
CLOSE my_cursor;
DEALLOCATE my_cursor;
END
IF(@isDirect = 1)
BEGIN
DECLARE my_cursor CURSOR FOR
SELECT crt.ID
FROM
bids b INNER JOIN tbl_EntityInstanceCallForTender e ON b.call_for_tender_ID = e.CallForTenderID
INNER JOIN tbl_ProcessCriterias pc ON e.EntityID = pc.EntityID
INNER JOIN tbl_Criterias c ON pc.ID = c.ProcessID
INNER JOIN tbl_CriteriaTypes ct ON c.OrderNumber = ct.ID
INNER JOIN CRITERIAS crt ON ct.OrderNumber = crt.number
WHERE b.id = @bid_Id and c.IsDeleted=0
UNION
SELECT crt.ID
FROM
bids b INNER JOIN tbl_EntityInstanceLotsCallForTender e ON b.call_for_tender_ID = e.CallForTenderID
INNER JOIN tbl_ProcessCriterias pc ON e.EntityID = pc.EntityID
INNER JOIN tbl_Criterias c ON pc.ID = c.ProcessID
INNER JOIN tbl_CriteriaTypes ct ON c.OrderNumber = ct.ID
INNER JOIN CRITERIAS crt ON ct.OrderNumber = crt.number
WHERE b.id = @bid_Id and c.IsDeleted=0
--New section for OtherCriteria
union
select drtc.CRITERIA_ID from BID_CALCULATIONS bc
inner join CRITERIAS c on bc.criteria_ID=c.ID
inner join BIDS b on b.ID=bc.bid_ID
inner join CALL_FOR_TENDERS cft on cft.id=b.call_for_tender_ID
inner join DECISIONS_ON_REQUESTS_FOR_TENDERS_CRITERIAS drtc on drtc.decision_for_tender_id=cft.decision_on_request_for_tender_ID and drtc.criteria_id=c.ID
where bid_ID=@bid_Id and drtc.points>0 and number>13
SET @bidPoints = 0.00;
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @criteriaID
IF(@@FETCH_STATUS = 0)
BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @valueTmp2 decimal(8,2);
SELECT @valueTmp2 = points FROM [dbo].[BID_CALCULATIONS] WHERE CRITERIA_ID = @criteriaID and BID_ID = @bid_Id;
SET @bidPoints = @bidPoints + @valueTmp2;
FETCH NEXT FROM my_cursor
INTO @criteriaID
END
END
CLOSE my_cursor;
DEALLOCATE my_cursor;
END
RETURN ISNULL(@bidPoints, 0.00);
END
Let me know guys if you need some more details i can explain, show etc...
Thanks again.