Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


rewrite cursor to set based


rewrite cursor to set based

Author
Message
priestxandar
priestxandar
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 265
Hi
Is it possible to rewrite this function which is using cursor with set based operations ? any example how to do will be greatly appreciated.
here is the function:


ALTER FUNCTION [dbo].[fn_GetPointsForBid]
(
   @bid_Id uniqueidentifier
)
RETURNS nvarchar(4000)
AS
BEGIN
   -- Declare the return variable here
   DECLARE @bidPoints nvarchar(4000);
   DECLARE @criteriaID uniqueidentifier;
   
   
   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
         DECLARE @textTmp nvarchar(100);
         DECLARE @valueTmp decimal(8,2);
         DECLARE @maxValueTmp decimal(8,2);
         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);
      
         FETCH NEXT FROM my_cursor
         INTO @criteriaID
      END
      SET @bidPoints = LTRIM(RTRIM(@bidPoints));
   END
   
   CLOSE my_cursor;
   DEALLOCATE my_cursor;
   RETURN ISNULL(@bidPoints, '');
END
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8474 Visits: 18082
I hope that you're ready for a great boost on performance. You didn't give any sample data, so there's a possibility that you won't get exactly what you're looking for but if you understand the code, you'll be able to adapt it.

There are 2 articles that you should read to understand this solution:
This one is to remove the cursor: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
This one is to make the function run faster: http://www.sqlservercentral.com/articles/T-SQL/91724/

Here's what I propose:

ALTER FUNCTION [dbo].[fn_GetPointsForBid]
(
   @bid_Id uniqueidentifier
)
RETURNS TABLE
AS
RETURN
   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
            ORDER BY CRITERIA_NUMBER
            FOR XML PATH(''), TYPE).value('.','nvarchar(4000)');



The code is a lot shorter than the cursor and will perform a lot better. The way to call the function will change as well and you'll need to use CROSS APPLY, but it's worth it.

If you have questions after reading the articles, feel free to ask.


Luis C.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Aditya Daruka
Aditya Daruka
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 173
Hello, I have tried my best to understand your code and come up with a solution which should work. I used xml path and few string manipulation.

Note: I could not test my sql due to the lack of test data. In future, it will be good if you include one.


-- Please provide a valid bid id
DECLARE @bid_Id UNIQUEIDENTIFIER = '????????????????????????????????????' ;

;WITH xmlListCTE(Value) AS
(
SELECT Value = LTRIM(RTRIM(bidC.CRITERIA_NAME)) + ' - ' + CONVERT(VARCHAR(10), bidC.Calc_Points) + '/' + CONVERT(VARCHAR(10), bidC.Max_points)
FROM [dbo].[vw_Bid_Criterias] bidC
WHERE bidC.BID_ID = @bid_Id
ORDER BY bidC.CRITERIA_NUMBER
FOR XML PATH ('')
)
SELECT Result = ISNULL(REPLACE(REPLACE(REPLACE(cte.Value, '</Value><Value>', CHAR(13)), '</Value>', ''), '<Value>', ''), '')
FROM xmlListCTE cte ;


Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8474 Visits: 18082
Aditya,
If you don't give an alias to the column, you don't need the nested REPLACEs. ;-)


Luis C.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
priestxandar
priestxandar
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 265
Luis Cazares (3/25/2014)
Aditya,
If you don't give an alias to the column, you don't need the nested REPLACEs. ;-)


how do you mean ? can you explain a bit ?
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8474 Visits: 18082
I'll try to explain it.
First, I'll create some sample data to follow along and keep it short.

CREATE TABLE #SampleData(
   Criteria_Number   int,
   Criteria_Name   varchar(50),
   Calc_Points      int,
   Max_Points      int);
   
INSERT #SampleData
SELECT 1, 'Criteria A', 5, 10 UNION ALL
SELECT 1, 'Criteria B', 13, 20 UNION ALL
SELECT 1, 'Criteria C', 70, 100;


If we run the following query using FOR XML PATH ('') and assign an alias to the column, we'll end up with the tags <Value></Value> for each row of our table.
SELECT bidPoints = (
   SELECT Value = LTRIM(RTRIM(CRITERIA_NAME)) + ' - '
         + CONVERT(varchar(10),Calc_Points ) + '/'
         + CONVERT(varchar(10),Max_points) + CHAR(13)
   FROM #SampleData
   ORDER BY CRITERIA_NUMBER
   FOR XML PATH(''));
--Result
--<Value>Criteria A - 5/10& #x0D;</Value><Value>Criteria B - 13/20& #x0D;</Value><Value>Criteria C - 70/100& #x0D;</Value>


If we remove the alias from the column, the tags will be gone as well because SQL Server won't know what field is it as it is an unnamed column

SELECT bidPoints = (
   SELECT LTRIM(RTRIM(CRITERIA_NAME)) + ' - '
         + CONVERT(varchar(10),Calc_Points ) + '/'
         + CONVERT(varchar(10),Max_points) + CHAR(13)
   FROM #SampleData
   ORDER BY CRITERIA_NUMBER
   FOR XML PATH(''));
--Result
--Criteria A - 5/10& #x0D;Criteria B - 13/20& #x0D;Criteria C - 70/100& #x0D;


Note that we still have a code representing the CHAR(13) (I added a space to it in this post to be able to show it). To remove the XML codes as this one or the ones for <, >, & and others, we use the TYPE and .value to avoid this characters being tokenized. Resulting on:
SELECT bidPoints = (
   SELECT LTRIM(RTRIM(CRITERIA_NAME)) + ' - '
         + CONVERT(varchar(10),Calc_Points ) + '/'
         + CONVERT(varchar(10),Max_points) + CHAR(13)
   FROM #SampleData
   ORDER BY CRITERIA_NUMBER
   FOR XML PATH(''), TYPE).value('.','nvarchar(4000)');
--Result
--Criteria A - 5/10
--Criteria B - 13/20
--Criteria C - 70/100
--


To avoid the final CHAR(13) we can change the order of the elements in our string concatenation and use STUFF to remove the first character.

SELECT bidPoints = STUFF((
   SELECT CHAR(13) + LTRIM(RTRIM(CRITERIA_NAME)) + ' - '
         + CONVERT(varchar(10),Calc_Points ) + '/'
         + CONVERT(varchar(10),Max_points)
   FROM #SampleData
   ORDER BY CRITERIA_NUMBER
   FOR XML PATH(''), TYPE).value('.','nvarchar(4000)'), 1, 1, '');



I hope that this becomes easier to understand.


Luis C.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
priestxandar
priestxandar
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 265
@SSCrazy

Thanks for explanation this was extremely helpful, couldn't find and ask for better explanation, thanks again you are awesome bro, great example!
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8474 Visits: 18082
priestxandar (3/26/2014)
@SSCrazy

Thanks for explanation this was extremely helpful, couldn't find and ask for better explanation, thanks again you are awesome bro, great example!


Thank you for the feedback. It's important that you understand every piece of the code because you'll have to support it.

Just note that the name is above the avatar and SSCrazy is my "rank" at this moment. ;-)


Luis C.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
priestxandar
priestxandar
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 265
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.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8474 Visits: 18082
Talk about RBAR on steroids and overcomplicating things. These cursors are just going row by row adding a value. This is totally unnecessary if you know about aggregate functions. In this case, you're looking for SUM().
Please show us what have you tried.


Luis C.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search