Hello again,

I've stacked on another cursor which is far more complicated for me. I would like to know if is possible to be rewritten to set-based and also to iTVF.

here is the actual function which contains 4 cursors inside:

ALTER FUNCTION [dbo].[fn_GetPointsForBid_WithoutPrice]

(

@bid_Id UNIQUEIDENTIFIER

)

RETURNS NVARCHAR(4000)

AS

BEGIN

-- Declare the return variable here

DECLARE @bidPoints NVARCHAR(4000);

DECLARE @criteriaID UNIQUEIDENTIFIER;

DECLARE @textTmp NVARCHAR(100);

DECLARE @valueTmp DECIMAL(8, 2);

DECLARE @maxValueTmp DECIMAL(8, 2);

DECLARE my_cursor CURSOR

FOR

SELECT CRITERIA_ID

FROM [dbo].[vw_Bid_Criterias]

WHERE BID_ID = @bid_Id

ORDER BY [dbo].[vw_Bid_Criterias].CRITERIA_NUMBER;

SET @bidPoints = '';

OPEN my_cursor

FETCH NEXT FROM my_cursor

INTO @criteriaID

IF ( @@FETCH_STATUS = 0 )

BEGIN

WHILE @@FETCH_STATUS = 0

BEGIN

IF @criteriaID <> '2628E084-035B-4237-A402-94AE31EE56FB'

BEGIN

SELECT @textTmp = CRITERIA_NAME

FROM [dbo].[vw_Bid_Criterias]

WHERE CRITERIA_ID = @criteriaID

AND BID_ID = @bid_Id;

SELECT @maxValueTmp = Max_points

FROM [dbo].[vw_Bid_Criterias]

WHERE CRITERIA_ID = @criteriaID

AND BID_ID = @bid_Id;

SELECT @valueTmp = Calc_Points

FROM [dbo].[vw_Bid_Criterias]

WHERE CRITERIA_ID = @criteriaID

AND BID_ID = @bid_Id;

SET @bidPoints = @bidPoints

+ LTRIM(RTRIM(@textTmp)) + ' - '

+ CONVERT(VARCHAR(10), @valueTmp) + '/'

+ CONVERT(VARCHAR(10), @maxValueTmp)

+ CHAR(13);

END

FETCH NEXT FROM my_cursor

INTO @criteriaID

END

SET @bidPoints = LTRIM(RTRIM(@bidPoints));

END

CLOSE my_cursor;

DEALLOCATE my_cursor;

IF ( @bidPoints IS NULL

OR @bidPoints = ''

)

BEGIN

DECLARE @numElements INT;

DECLARE @ProcessID UNIQUEIDENTIFIER;

SELECT @numElements = COUNT(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;

IF ( @numElements > 0 )

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-- or (c.TypeID=2 and c.Value>0)

INNER JOIN CRITERIAS crt ON ct.OrderNumber = crt.number

WHERE b.id = @bid_Id

SELECT @ProcessID = ID

FROM tbl_ProcessCriterias

WHERE EntityID = ( SELECT EntityID

FROM tbl_EntityInstanceCallForTender

WHERE CallForTenderID = ( SELECT

call_for_tender_ID

FROM

bids

WHERE

id = @bid_Id

)

)

SET @bidPoints = '';

OPEN my_cursor

FETCH NEXT FROM my_cursor

INTO @criteriaID

IF ( @@FETCH_STATUS = 0 )

BEGIN

WHILE @@FETCH_STATUS = 0

BEGIN

IF @criteriaID <> '2628E084-035B-4237-A402-94AE31EE56FB'

BEGIN

DECLARE @tmpOrderNumber INT;

SELECT @textTmp = NAME ,

@tmpOrderNumber = number

FROM [dbo].[CRITERIAS]

WHERE ID = @criteriaID;

SELECT @maxValueTmp = c.Value

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 ct.OrderNumber = @tmpOrderNumber

ORDER BY crt.number

SELECT @valueTmp = points

FROM [dbo].[BID_CALCULATIONS]

WHERE CRITERIA_ID = @criteriaID

AND BID_ID = @bid_Id;

SET @bidPoints = @bidPoints

+ LTRIM(RTRIM(@textTmp))

+ ' - '

+ CONVERT(VARCHAR(10), @valueTmp)

+ '/'

+ CONVERT(VARCHAR(10), @maxValueTmp)

+ CHAR(13);

END

FETCH NEXT FROM my_cursor

INTO @criteriaID

END

SET @bidPoints = LTRIM(RTRIM(@bidPoints));

END

CLOSE my_cursor;

DEALLOCATE my_cursor;

--New for Criterias Other BEGIN

DECLARE my_cursor CURSOR

FOR

SELECT drtc.description + '-'

+ CONVERT(VARCHAR(10), bc.points) + '/'

+ CONVERT(VARCHAR(10), drtc.points) AS p1

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

OPEN my_cursor

FETCH NEXT FROM my_cursor

INTO @textTmp

IF ( @@FETCH_STATUS = 0 )

BEGIN

WHILE @@FETCH_STATUS = 0

BEGIN

SET @bidPoints = @bidPoints + @textTmp

END

END

CLOSE my_cursor;

DEALLOCATE my_cursor;

--END New for Criterias

END

ELSE

BEGIN

DECLARE my_cursor CURSOR

FOR

SELECT crt.ID

FROM bids b

INNER JOIN tbl_EntityInstanceLotsCallForTender e ON b.call_for_tender_ID = e.CallForTenderID

AND e.isDeleted = 0

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

ORDER BY crt.number;

SELECT @ProcessID = ID

FROM tbl_ProcessCriterias

WHERE EntityID = ( SELECT TOP 1

EntityID

FROM tbl_EntityInstanceLotsCallForTender

WHERE CallForTenderID = ( SELECT

call_for_tender_ID

FROM

bids

WHERE

id = @bid_Id

)

AND isdeleted = 0

)

SET @bidPoints = '';

OPEN my_cursor

FETCH NEXT FROM my_cursor

INTO @criteriaID

IF ( @@FETCH_STATUS = 0 )

BEGIN

WHILE @@FETCH_STATUS = 0

BEGIN

IF @criteriaID <> '2628E084-035B-4237-A402-94AE31EE56FB'

BEGIN

DECLARE @tmpOrderNumber2 INT;

SELECT @textTmp = NAME ,

@tmpOrderNumber2 = number

FROM [dbo].[CRITERIAS]

WHERE ID = @criteriaID;

SELECT @maxValueTmp = c.Value

FROM bids b

INNER JOIN tbl_EntityInstanceLotsCallForTender e ON b.call_for_tender_ID = e.CallForTenderID

AND e.isdeleted = 0

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 ct.OrderNumber = @tmpOrderNumber2

ORDER BY crt.number

SELECT @valueTmp = points

FROM [dbo].[BID_CALCULATIONS]

WHERE CRITERIA_ID = @criteriaID

AND BID_ID = @bid_Id;

SET @bidPoints = @bidPoints

+ LTRIM(RTRIM(@textTmp))

+ ' - '

+ CONVERT(VARCHAR(10), @valueTmp)

+ '/'

+ CONVERT(VARCHAR(10), @maxValueTmp)

+ CHAR(13);

END

FETCH NEXT FROM my_cursor

INTO @criteriaID

END

SET @bidPoints = LTRIM(RTRIM(@bidPoints));

--SET @bidPoints = SUBSTRING(@bidPoints, 1, LEN(@bidPoints) - 1);

END

CLOSE my_cursor;

DEALLOCATE my_cursor;

END

END

RETURN ISNULL(@bidPoints, '');

END

I've tried to rewrite it but couldn't manage to do it.

Here is my code snippet also:

CREATE FUNCTION [dbo].[fn_GetPointsForBid_WithoutPriceTEST2]

(

@bid_Id UNIQUEIDENTIFIER

)

RETURNS TABLE

AS

RETURN

-- this is first cursor

SELECT bidPoints = (

SELECT LTRIM(RTRIM(CRITERIA_NAME)) + ' - '

+ CONVERT(varchar(10),Calc_Points ) + '/'

+ CONVERT(varchar(10),Max_points) + CHAR(13)

FROM [dbo].[vw_Bid_Criterias]

WHERE BID_ID = @bid_Id and CRITERIA_ID <> '2628E084-035B-4237-A402-94AE31EE56FB'

ORDER BY CRITERIA_NUMBER

FOR XML PATH(''), TYPE).value('.','nvarchar(4000)')

-- i believe this is done correctly

-- first cursor ends here

-------------------------------------------------------------------------------------------------------------

-- second cursor starts here

-- i can't start like this but don't know how

CASE bidPoints WHEN IS NULL OR ''

THEN (SELECT bidPoints = (

SELECT LTRIM(RTRIM(CRITERIA_NAME)) + ' - '

+ CONVERT(varchar(10),Calc_Points ) + '/'

+ CONVERT(varchar(10),Max_points) + CHAR(13)

FROM [dbo].[vw_Bid_Criterias]

WHERE BID_ID = @bid_Id AND dbo.vw_Bid_Criterias.CRITERIA_ID <> '2628E084-035B-4237-A402-94AE31EE56FB'

-- and dont have idea what to do here

-- second cursor ends here

----------------------------------------------------------------------------------------------------------------

-- third cursor starts here

SELECT bidPoints = (

SELECT drtc.description + '-' + CONVERT(VARCHAR(10), bc.points)+'/'+ CONVERT(VARCHAR(10), drtc.points) as p1

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

)

-- maybe this cursor is good, but dont know how to connect with others

-- third cursor ends here

--------------------------------------------------------------------------------------------------------------------

-- fourth cursor starts here

SELECT bidPoints = ( SELECT

+ LTRIM(RTRIM(@textTmp))

+ ' - '

+ CONVERT(VARCHAR(10), @valueTmp)

+ '/'

+ CONVERT(VARCHAR(10), @maxValueTmp)

+ CHAR(13)

FROM

-- here i dont know how to read the values because they are from different tables,

-- also dont know from which main table i should get the data

-- and i got lost totally

Maybe order of the cursor should be changed, couldnt find the logic how to manage this.

Please let me know about any thoughts.

Regards

anyone with suggestion ???