March 31, 2014 at 7:45 am
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);
March 31, 2014 at 9:22 am
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 🙂
March 31, 2014 at 10:56 am
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. 😉
April 1, 2014 at 11:49 am
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 ???
April 5, 2014 at 8:13 am
Could anyone help with this ?
Thank you
April 5, 2014 at 11:16 am
priestxandar (4/5/2014)
Could anyone help with this ?Thank you
It's a fair bit of work to rewrite such things so that they perform better. It's also a bit like shooting in the dark because we don't have your tables, indexes, data, etc, etc to go on.
My recommendation would be to hire a consultant (and I mean a good one... not just anyone) to fix these occasional monstrosities or a full-time really good application DBA or super database Developer if you need more than occasional help with these things.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply