January 17, 2017 at 9:54 am
Hi,
I have been tasked with updating all functions that use cursors to use FOR XML instead. I have converted some standard ones, but I am not sure what to do when I have functions with nested variables as per the example below. I have tried to dumb down the example below as much as possible.
I get the basics in terms of SELECT @l_strRetVal = COALESCE(( SELECT.. etc and adding FOR XML PATH('') ), '' ) to the end of each union, but I am struggling with the FETCH syntax as I don't know how this fits into the FOR XML. I am not sure how I convert the variables in the FETCH part of the function below and any help or guidance would be appreciated...
CREATE FUNCTION dba.spd_ListFoldRemarks ( @strBranchCode CHAR( 2 ), @lFoldNo INT, @strDocType VARCHAR(30 ), @strLanguage VARCHAR( 50 ))
RETURNS VARCHAR ( MAX )
BEGIN
DECLARE @l_strRetVal VARCHAR ( MAX )
DECLARE @l_strRemarkType VARCHAR ( 100 )
DECLARE @l_strFoldRemark VARCHAR ( MAX )
DECLARE @l_strFoldAdditionalRemark VARCHAR ( MAX )
DECLARE @l_strTempRemarkType VARCHAR( 100 )
DECLARE FoldRemarksCursor CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
SELECT strRtName_FD, strRPrintingNote_FD, strRAdditionalPrintingNote_FD FROM (
SELECT -- ARRIVAL POINT STUFF WITH START POINT CODE
strRtName_FD,
strRPrintingNote_FD,
strRAdditionalPrintingNote_FD,
nRtOrder_FD,
lROrder_FD,
dtRValidFromDate_FD,
dtRValidToDate_FD,
dtRConsumptionFromDate_FD,
dtRConsumptionToDate_FD,
FI.dtFiCreateDate_FD,
FI.strBBranchCode_FD,
FI.lFFoldNo_FD,
FI.strFiType_FD,
FI.strFiEndPointCode_FD,
FI.dtFiEndDateTime_FD,
FI.dtFiStartDateTime_FD
FROM DBA.FoldItemsPreVendDec_VW AS FI WITH( NOLOCK )
JOIN DBA.Status_TB AS S WITH( NOLOCK )
ON FI.strFiStatus_FD = S.StatusCode_FD
WHERE FI.strBBranchCode_FD = @strBranchCode
AND FI.lFFoldNo_FD = @lFoldNo
AND COALESCE( APAR.strDtDocType_FD, '' ) = @strDocType
AND COALESCE( APAR.bAparPrintForItem_FD, '' ) = 0
AND RRT.strRtLanguage_FD = @strLanguage
AND RRT.strBBranchCode_FD = @strBranchCode
AND nSStatusType_FD <> 2
AND bFiStartPointIsTransfer_FD <> 1
UNION
SELECT -- CLASS STUFF
strRtName_FD,
strRPrintingNote_FD,
strRAdditionalPrintingNote_FD,
nRtOrder_FD,
lROrder_FD,
dtRValidFromDate_FD,
dtRValidToDate_FD,
dtRConsumptionFromDate_FD,
dtRConsumptionToDate_FD,
FI.dtFiCreateDate_FD,
FI.strBBranchCode_FD,
FI.lFFoldNo_FD,
FI.strFiType_FD,
FI.strFiEndPointCode_FD,
FI.dtFiEndDateTime_FD,
FI.dtFiStartDateTime_FD
FROM DBA.AirSegClasses_VW FI WITH( NOLOCK )
INNER LOOP JOIN DBA.ClassAutoRemarkAssociationDocTypesEdObjs_VW AS CAR WITH( NOLOCK )
ON CAR.ClassCode_FD = FI.strClassCode_FD
AND CAR.Airline_FD = FI.strAirlineCode_FD
AND CAR.strCarFareBasis_FD = FI.strFiFareBase_FD
WHERE FI.strBBranchCode_FD = @strBranchCode
AND FI.lFFoldNo_FD = @lFoldNo
AND COALESCE( CAR.strDtDocType_FD, '' ) = @strDocType
AND COALESCE( CAR.bCarPrintForItem_FD, '' ) = 0
AND COALESCE( RRT.strRtLanguage_FD, '' ) = @strLanguage
AND COALESCE( RRT.strBBranchCode_FD, '' ) = @strBranchCode
) AS Remark_DTB -- This is the derived table (_DTB) for the folder reamrks to enable ordering on the nRtOrder_FD
WHERE(dtFiCreateDate_FD <= dtRValidToDate_FD)
GROUP BY strRtName_FD, strRPrintingNote_FD, strRAdditionalPrintingNote_FD, nRtOrder_FD, lROrder_FD
ORDER BY nRtOrder_FD, lROrder_FD
SELECT @l_strRetVal = ''
SELECT @l_strTempRemarkType = ''
OPEN FoldRemarksCursor;
FETCH FoldRemarksCursor INTO @l_strRemarkType, @l_strFoldRemark, @l_strFoldAdditionalRemark
WHILE @@FETCH_STATUS = 0
BEGIN
IF @l_strTempRemarkType <> @l_strRemarkType
BEGIN
IF COALESCE( @l_strRemarkType, '' ) <> ''
BEGIN
SELECT @l_strRetVal = @l_strRetVal + CHAR( 13 ) + COALESCE ( @l_strRemarkType, '' ) +
CASE WHEN CHARINDEX( '<HTML>', @l_strRemarkType) > 0 THEN '' ELSE ': ' END
+ CHAR( 13 )
SELECT @l_strTempRemarkType = @l_strRemarkType;
END
END
IF COALESCE( @l_strFoldRemark, '' ) <> ''
BEGIN
SELECT @l_strRetVal = @l_strRetVal + COALESCE ( @l_strFoldRemark, '' ) + CHAR( 13 )
END
IF COALESCE( @l_strFoldAdditionalRemark, '' ) <> ''
BEGIN
SELECT @l_strRetVal = @l_strRetVal + COALESCE ( @l_strFoldAdditionalRemark, '' ) + CHAR( 13 )
END
FETCH FoldRemarksCursor INTO @l_strRemarkType, @l_strFoldRemark, @l_strFoldAdditionalRemark
END
CLOSE FoldRemarksCursor
SELECT @l_strRetVal = @l_strRetVal + CHAR( 13 )
IF CHARINDEX( '<HTML>', @l_strRetVal) > 0
BEGIN
SELECT @l_strRetVal = '<HTML>' + REPLACE( @l_strRetVal, CHAR( 13 ), '<P> ' )
END
RETURN( @l_strRetVal )
END
GO
January 17, 2017 at 10:17 am
Could you provide some sample of what you're expecting? Some notional test data would be nice as well.
I suspect you don't need the cursor at all and that you're trying to hand code XML for some reason.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 17, 2017 at 10:29 am
Matt Miller (#4) - Tuesday, January 17, 2017 10:17 AMCould you provide some sample of what you're expecting? Some notional test data would be nice as well.I suspect you don't need the cursor at all and that you're trying to hand code XML for some reason.
Hi, the function returns all remarks that are associated to a booking. The remarks are related to the booking; they can be class remarks (so remarks related to the ticket class that is booked), or Destination remarks or vendor remarks etc. The remarks can be configured to include a remark type or not. For example a ticket to Madrid could contain the following remarks:
Flight: (remark type optional)
Here are the remaks that relate to the vendor regarding your flight from LHR to MAD
Class
You have booked club class with extra leg room
Madrid
In Madrid you can book a city sight seeing tour for your party.
etc....
So we collate all the related remarks to the booking and related class, vendor, destinatination etc.
Does that help?
January 17, 2017 at 10:34 am
Matt Miller (#4) - Tuesday, January 17, 2017 10:17 AMCould you provide some sample of what you're expecting? Some notional test data would be nice as well.I suspect you don't need the cursor at all and that you're trying to hand code XML for some reason.
Sorry, I forgot to include; We are trying to get away from using cursors because of the related performance issues..
Thanks
J
January 17, 2017 at 10:41 am
James Millar-305032 - Tuesday, January 17, 2017 10:29 AMMatt Miller (#4) - Tuesday, January 17, 2017 10:17 AMCould you provide some sample of what you're expecting? Some notional test data would be nice as well.I suspect you don't need the cursor at all and that you're trying to hand code XML for some reason.
Hi, the function returns all remarks that are associated to a booking. The remarks are related to the booking; they can be class remarks (so remarks related to the ticket class that is booked), or Destination remarks or vendor remarks etc. The remarks can be configured to include a remark type or not. For example a ticket to Madrid could contain the following remarks:
Flight: (remark type optional)
Here are the remaks that relate to the vendor regarding your flight from LHR to MAD
Class
You have booked club class with extra leg room
Madrid
In Madrid you can book a city sight seeing tour for your party.etc....
So we collate all the related remarks to the booking and related class, vendor, destinatination etc.
Does that help?
No, we are looking for sample data (as table definitions with insert statements) and expected results from that sample data (again as table definitions with insert statements) or if you only expect a single xml document, you can specify it as an XML variable. You can find more information in the link in my signature.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 17, 2017 at 11:32 am
drew.allen - Tuesday, January 17, 2017 10:41 AMJames Millar-305032 - Tuesday, January 17, 2017 10:29 AMMatt Miller (#4) - Tuesday, January 17, 2017 10:17 AMCould you provide some sample of what you're expecting? Some notional test data would be nice as well.I suspect you don't need the cursor at all and that you're trying to hand code XML for some reason.
Hi, the function returns all remarks that are associated to a booking. The remarks are related to the booking; they can be class remarks (so remarks related to the ticket class that is booked), or Destination remarks or vendor remarks etc. The remarks can be configured to include a remark type or not. For example a ticket to Madrid could contain the following remarks:
Flight: (remark type optional)
Here are the remaks that relate to the vendor regarding your flight from LHR to MAD
Class
You have booked club class with extra leg room
Madrid
In Madrid you can book a city sight seeing tour for your party.etc....
So we collate all the related remarks to the booking and related class, vendor, destinatination etc.
Does that help?
No, we are looking for sample data (as table definitions with insert statements) and expected results from that sample data (again as table definitions with insert statements) or if you only expect a single xml document, you can specify it as an XML variable. You can find more information in the link in my signature.
Drew
Maybe I am not explaining myself very well. Unfortunately the guy who usually deals with this type of work is no longer with us, so this has been left to me so apologies for my lack of knowledge in this area. The functions I am attempting to update are referenced in database views which are then used as the basis for reporting the data. If I give you an example of a function that I have converted from a cursor-based function then maybe this will help explain the context. Here is a function that I have modified successfully:
ALTER FUNCTION dba.fn_ListPbVendCommsSpecialRequestData ( @lPbBookingNo INT )
RETURNS VARCHAR ( MAX )
BEGIN
DECLARE @l_strRetVal VARCHAR ( MAX )
SELECT @l_strRetVal = COALESCE(( SELECT
LTRIM( RTRIM( strPbvcdLabelName_FD )) + CHAR( 10 )
FROM DBA.ProductBookingVendCommsData_TB AS PBVCD
WHERE PBVCD.lPbBookingNo_FD = @lPbBookingNo
AND PBVCD.nPbvcdBookingProfileType_FD = 1 -- special request data only
ORDER BY PBVCD.nPbvcdDisplayOrder_FD FOR XML PATH('') ), '' )
RETURN LTRIM( RTRIM( @l_strRetVal ))
END
GO
I am looking to adapt this logic to the more complex function that I posted that contains "nested variables" (sorry not sure if this is the correct terminology but I am referring to @l_strRemarkType, @l_strFoldAdditionalRemark etc that appear in the FETCH part of the existing function.) I am not sure how I would need to adapt this syntax to fit into the FOR XML approach I am trying to implement. If this is still not making any sense then I apologize for wasting you time.
Thanks,
J
January 17, 2017 at 11:38 am
James Millar-305032 - Tuesday, January 17, 2017 11:32 AMdrew.allen - Tuesday, January 17, 2017 10:41 AMJames Millar-305032 - Tuesday, January 17, 2017 10:29 AMMatt Miller (#4) - Tuesday, January 17, 2017 10:17 AMCould you provide some sample of what you're expecting? Some notional test data would be nice as well.I suspect you don't need the cursor at all and that you're trying to hand code XML for some reason.
Hi, the function returns all remarks that are associated to a booking. The remarks are related to the booking; they can be class remarks (so remarks related to the ticket class that is booked), or Destination remarks or vendor remarks etc. The remarks can be configured to include a remark type or not. For example a ticket to Madrid could contain the following remarks:
Flight: (remark type optional)
Here are the remaks that relate to the vendor regarding your flight from LHR to MAD
Class
You have booked club class with extra leg room
Madrid
In Madrid you can book a city sight seeing tour for your party.etc....
So we collate all the related remarks to the booking and related class, vendor, destinatination etc.
Does that help?
No, we are looking for sample data (as table definitions with insert statements) and expected results from that sample data (again as table definitions with insert statements) or if you only expect a single xml document, you can specify it as an XML variable. You can find more information in the link in my signature.
Drew
Maybe I am not explaining myself very well. Unfortunately the guy who usually deals with this type of work is no longer with us, so this has been left to me so apologies for my lack of knowledge in this area. The functions I am attempting to update are referenced in database views which are then used as the basis for reporting the data. If I give you an example of a function that I have converted from a cursor-based function then maybe this will help explain the context. Here is a function that I have modified successfully:
ALTER FUNCTION dba.fn_ListPbVendCommsSpecialRequestData ( @lPbBookingNo INT )
RETURNS VARCHAR ( MAX )
BEGIN
DECLARE @l_strRetVal VARCHAR ( MAX )
SELECT @l_strRetVal = COALESCE(( SELECT
LTRIM( RTRIM( strPbvcdLabelName_FD )) + CHAR( 10 )
FROM DBA.ProductBookingVendCommsData_TB AS PBVCD
WHERE PBVCD.lPbBookingNo_FD = @lPbBookingNo
AND PBVCD.nPbvcdBookingProfileType_FD = 1 -- special request data only
ORDER BY PBVCD.nPbvcdDisplayOrder_FD FOR XML PATH('') ), '' )
RETURN LTRIM( RTRIM( @l_strRetVal ))
END
GOI am looking to adapt this logic to the more complex function that I posted that contains "nested variables" (sorry not sure if this is the correct terminology but I am referring to @l_strRemarkType, @l_strFoldAdditionalRemark etc that appear in the FETCH part of the existing function.) I am not sure how I would need to adapt this syntax to fit into the FOR XML approach I am trying to implement. If this is still not making any sense then I apologize for wasting you time.
Thanks,
J
We asked for sample DATA, not sample code. Please read the first link in my signature.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 17, 2017 at 12:23 pm
I could post an untested solution, but why give you the fish when I can share a fishing course with you. Take a look at this article:
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
January 17, 2017 at 1:15 pm
Luis Cazares - Tuesday, January 17, 2017 12:23 PMI could post an untested solution, but why give you the fish when I can share a fishing course with you. Take a look at this article:
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Appreciated! I'll check it out...
January 17, 2017 at 1:50 pm
You might also take a look at this article before continuing to splatter NOLOCK hints everywhere. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply