Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

rewrite cursor to set based Expand / Collapse
Author
Message
Posted Thursday, March 27, 2014 8:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:50 AM
Points: 5,370, Visits: 9,018
Well, hello there.

Luis - thanks for linking to my article. However, I'm afraid that it doesn't do much for helping to learn how to remove cursors. For that task, my favorite articles can be found here - it should be obvious which two articles I'm referring to.

priestxandar - This latest cursor-ridden function just makes we want to say "OMG". For several reasons.
1. It's a scalar function. These don't scale well (nor do multi-statement table-valued functions). Please read this blog post for more information. If you can convert these scalar functions to inline table-valued functions, your server will be able to take a deep sigh of relief.
2. We don't have the definitions for the views that are referenced. Please script out and post the DDL for the views vw_CallForTender, [vw_Bid_Criterias] and any other views referenced in this function.
3. How are these functions being used? If you can include those queries, I bet we can make the entire process even better!
4. Luis and I both have links in our signatures for how to post data to get better help. It really does make a difference. Please do this. Or...
5. Consider hiring someone (like myself) to look at your system and re-write this code. It seems like you are in over your head, and this may well be the better method.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1555741
Posted Friday, March 28, 2014 9:10 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:13 AM
Points: 4,067, Visits: 9,235
WayneS (3/27/2014)
Well, hello there.

Luis - thanks for linking to my article. However, I'm afraid that it doesn't do much for helping to learn how to remove cursors. For that task, my favorite articles can be found here - it should be obvious which two articles I'm referring to.

I pointed to your article because it really helped with the first cursor. I agree that Barry's articles are great to learn how to remove cursors (it's a shame that he didn't complete the series).
Something that changes minds towards set based programming is the following phrase:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1555989
Posted Friday, March 28, 2014 5:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:30 AM
Points: 20, Visits: 228
WayneS (3/27/2014)
Well, hello there.

Luis - thanks for linking to my article. However, I'm afraid that it doesn't do much for helping to learn how to remove cursors. For that task, my favorite articles can be found here - it should be obvious which two articles I'm referring to.

priestxandar - This latest cursor-ridden function just makes we want to say "OMG". For several reasons.
1. It's a scalar function. These don't scale well (nor do multi-statement table-valued functions). Please read this blog post for more information. If you can convert these scalar functions to inline table-valued functions, your server will be able to take a deep sigh of relief.
2. We don't have the definitions for the views that are referenced. Please script out and post the DDL for the views vw_CallForTender, [vw_Bid_Criterias] and any other views referenced in this function.
3. How are these functions being used? If you can include those queries, I bet we can make the entire process even better!
4. Luis and I both have links in our signatures for how to post data to get better help. It really does make a difference. Please do this. Or...
5. Consider hiring someone (like myself) to look at your system and re-write this code. It seems like you are in over your head, and this may well be the better method.


1. First to say thanks you are trying to help me, this articles help me to understand lot of things. If i understood good, this function can not be rewritten to inline table valued because is using parameters and can not be done in single select statement.
2. As i mention before its over complicated to include test data, those views have nested views and again nested views, also and lot of joins ( very very bad design). ( i could try to script out the views, and the nested views :) also and the stored procedure if that helps but i doubt)
3. This function is called in stored procedure in select statement, this function should get back SUM based on some criteria (if you look on the joins in function you will notice that).
This is the part where i'm confused most.
4. I'm new to this forum and also to sql server, i'm starting my career now, and i'm trying to learn as much as possible.
5. I'm not in position to hire anybody, i need to learn as much as i can and apply that knowledge.

it will be good if someone could make some general query/idea how could be rewritten based on the info we have right now, i will try to script out those views and proc in monday.

thanks again for being nice and helpful.
Post #1556178
Posted Friday, March 28, 2014 6:06 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:13 AM
Points: 4,067, Visits: 9,235
This function can certainly be converted into an inline table-valued function. It's tricky, but it can be done and it has nothing to do with parameters as the previous one used parameters as well.
I wanted you to show what have you done even if it's incorrect to be sure that you're understanding what's going on and have some feedback of your progression. I could give you the fish, but I prefer to teach you how to fish.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1556180
Posted Monday, March 31, 2014 4:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:30 AM
Points: 20, Visits: 228
Luis Cazares (3/28/2014)
This function can certainly be converted into an inline table-valued function. It's tricky, but it can be done and it has nothing to do with parameters as the previous one used parameters as well.
I wanted you to show what have you done even if it's incorrect to be sure that you're understanding what's going on and have some feedback of your progression. I could give you the fish, but I prefer to teach you how to fish.


Here is the code i wrote:

-- DECLARE @bid_Id UNIQUEIDENTIFIER;
-- SET @bid_Id = '475C1532-20CC-437A-9642-B2A87D79036A'

ALTER FUNCTION [dbo].[fn_GetSumOfPointsForBidTEST1]
(
-- Add the parameters for the function here
@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
SET @bidPoints = 0.00
DECLARE @valueTmp decimal(8,2);
SELECT @valueTmp = Calc_Points FROM [dbo].[vw_Bid_Criterias]
WHERE BID_ID = @bid_Id AND
CRITERIA_ID IN ( 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
)
SELECT @bidPoints = @bidPoints + @valueTmp

END

IF ( @isDirect = 1 )
BEGIN
SET @bidPoints = 0.00
DECLARE @valueTmp2 decimal(8,2);
SELECT @valueTmp2 = points FROM [dbo].[BID_CALCULATIONS]

WHERE bid_ID = @bid_Id AND
CRITERIA_ID IN ( 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
)

SELECT @bidPoints = @bidPoints + @valueTmp2
END
RETURN ISNULL(@bidPoints, 0.00);

END

code is working but i'm not getting same results comparing with the cursor. I've tested 50 values and only one was different.
example:
cursor value was 85 points
set-based value was 170 points
(this looks to me like somehow points are calculated twice)

anyway, am i to right path ? or i'm doing some non-sense


Thanks




UPDATE!
I found the issue about that, it was calculating and non-active bids so i've added filter to look only for active ( but the strange thing is that only on that particular bid_id was calculating and non-active values.)

anyway let me know if this is the right way i'm doing, because i've made some tests with statistic client ON and i dont see performance benefit.
Post #1556414
Posted Monday, March 31, 2014 7:45 AM This worked for the OP Answer marked as solution


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:13 AM
Points: 4,067, Visits: 9,235
It seems almost fine, but it's weird. You're not summing up the values. I added the SUM() and converted it to a iTVF. You need to test it as well to be sure it works as it should.

--	DECLARE @bid_Id UNIQUEIDENTIFIER;
-- SET @bid_Id = '475C1532-20CC-437A-9642-B2A87D79036A'

ALTER FUNCTION [dbo].[fn_GetSumOfPointsForBidTEST1]
(
-- Add the parameters for the function here
@bid_Id UNIQUEIDENTIFIER
)
RETURNS TABLE
AS
RETURN

SELECT TOP 1 CASE IsDirectAuction
WHEN 0 THEN (SELECT SUM(Calc_Points) FROM [dbo].[vw_Bid_Criterias]
WHERE BID_ID = @bid_Id AND
CRITERIA_ID IN ( 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
))
WHEN 1 THEN (SELECT SUM(points) FROM [dbo].[BID_CALCULATIONS]
WHERE bid_ID = @bid_Id AND
CRITERIA_ID IN ( 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
))
ELSE 0 END AS points
FROM vw_CallForTender
WHERE ID IN ( SELECT call_for_tender_ID
FROM BIDS
WHERE ID = @bid_Id);







Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1556467
Posted Monday, March 31, 2014 9:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:30 AM
Points: 20, Visits: 228
Luis Cazares (3/31/2014)
It seems almost fine, but it's weird. You're not summing up the values. I added the SUM() and converted it to a iTVF. You need to test it as well to be sure it works as it should.

--	DECLARE @bid_Id UNIQUEIDENTIFIER;
-- SET @bid_Id = '475C1532-20CC-437A-9642-B2A87D79036A'

ALTER FUNCTION [dbo].[fn_GetSumOfPointsForBidTEST1]
(
-- Add the parameters for the function here
@bid_Id UNIQUEIDENTIFIER
)
RETURNS TABLE
AS
RETURN

SELECT TOP 1 CASE IsDirectAuction
WHEN 0 THEN (SELECT SUM(Calc_Points) FROM [dbo].[vw_Bid_Criterias]
WHERE BID_ID = @bid_Id AND
CRITERIA_ID IN ( 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
))
WHEN 1 THEN (SELECT SUM(points) FROM [dbo].[BID_CALCULATIONS]
WHERE bid_ID = @bid_Id AND
CRITERIA_ID IN ( 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
))
ELSE 0 END AS points
FROM vw_CallForTender
WHERE ID IN ( SELECT call_for_tender_ID
FROM BIDS
WHERE ID = @bid_Id);








With iTFV have greater performance benefit. (will see the results on production environment) Also i learned now how to use CASE statement instead those IF.
still cant figure out how this code is not needed (removed)
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;

but i'll figure out.

Thanks again for helping, you are awesome tutor bro, i'm starting to think set-based now
Post #1556532
Posted Monday, March 31, 2014 10:56 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:13 AM
Points: 4,067, Visits: 9,235
The code is still there. It's just located at the end instead of the beginning.

I'm glad that you're helping. I'm just trying to pay it forward.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1556600
Posted Tuesday, April 1, 2014 11:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:30 AM
Points: 20, Visits: 228
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 ???
Post #1557143
Posted Saturday, April 5, 2014 8:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:30 AM
Points: 20, Visits: 228
Could anyone help with this ?

Thank you
Post #1558734
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse