November 17, 2006 at 4:19 am
Hi,
Following on from my "Error in stored procs" post yesterday i have been advised to give cursors a W-I-D-E berth. The reason i was using a cursor was because this is traditionally how we would generate lists of data in Sybase ASA7. My application runs on both ASA7 and SQL Server DBMS's, so historically we've tried to use syntax which i compatible on both to save writing and maintaining 2 script for all views and stored procs.
Now that's out of the way here is what i am trying to achieve - hopefully some bright spark out there can help me out while teaching me some valuable SQL Server lessons in the process!
Basically what we have is a remarks table (Remarks_TB) and four assocaition tables (ArrivalPointAutoRemarks_TB, DestinationAutoRemarks_TB, CountryAutoRemarks_TB and VendorAutoRemarks_TB) and i need to list out the remarks where there is an association in any of the child tables - this is what i was doing tro give you a better idea as my explanation is probably leaving you scratching you head!
***********
CREATE FUNCTION dba.spd_ListFoldRemarks (
@ArrivalPoint VARCHAR ( 4 ),
@DocType VARCHAR(30),
@VendID INTEGER )
RETURNS VARCHAR ( 8000 )
BEGIN
DECLARE @l_strRetVal VARCHAR ( 8000 )
DECLARE @l_strFoldRemark VARCHAR ( 100 )
DECLARE @l_bFirstRow SMALLINT
DECLARE FoldRemarksCursor CURSOR FOR
--Arrival Point stuff
SELECT
strRPrintingNote_FD,
strRtName_FD,
nRtOrder_FD
FROM
DBA.Remarks_TB R
JOIN DBA.RemarkTypes_TB RT
ON RT.nRtRemarkTypeID_FD = R.nRtRemarkTypeID_FD
JOIN DBA.ArrivalPointAutoRemarks_TB AS APAR
ON R.nRRemarkID_FD = APAR.nRRemarkID_FD
WHERE APAR.strApArrivalPointCode_FD = @ArrivalPoint
AND APAR.strDtDocType_FD = @DocType
UNION
--Destination stuff
SELECT
strRPrintingNote_FD,
strRtName_FD,
nRtOrder_FD
FROM
DBA.Remarks_TB R
JOIN DBA.RemarkTypes_TB RT
ON RT.nRtRemarkTypeID_FD = R.nRtRemarkTypeID_FD
JOIN DBA.DestinationAutoRemarks_TB AS DAR
ON DAR.nRRemarkID_FD = R.nRRemarkID_FD
JOIN DBA.ArrivalPoints_TB AS AP
ON DAR.nDDestinationID_FD = AP.iDestinationID_FD
WHERE AP.strArrivalPointCode_FD = @ArrivalPoint
AND DAR.strDtDocType_FD = @DocType
UNION
--Country stuff
SELECT
strRPrintingNote_FD,
strRtName_FD,
nRtOrder_FD
FROM
DBA.Remarks_TB R
JOIN DBA.RemarkTypes_TB AS RT
ON R.nRtRemarkTypeID_FD = R.nRtRemarkTypeID_FD
JOIN DBA.CountryAutoRemarks_TB AS CAR
ON R.nRRemarkID_FD = CAR.nRRemarkID_FD
JOIN DBA.Destinations_TB AS D
ON D.strCountryCode_FD = CAR.strCCountryCode_FD
JOIN DBA.ArrivalPoints_TB AS Ap
ON D.iDestinationID_FD = Ap.iDestinationID_FD
WHERE Ap.strArrivalPointCode_FD = @ArrivalPoint
AND CAR.strDtDocType_FD = @DocType
UNION
--Vendor stuff
SELECT
strRPrintingNote_FD,
strRtName_FD,
nRtOrder_FD
FROM
DBA.Remarks_TB R
JOIN DBA.RemarkTypes_TB RT
ON R.nRtRemarkTypeID_FD = R.nRtRemarkTypeID_FD
JOIN DBA.VendorAutoRemarks_TB VAR
ON R.nRRemarkID_FD = VAR.nRRemarkID_FD
WHERE VAR.lVVendID_FD = @VendID
AND VAR.strDtDocType_FD = @DocType
ORDER BY nRtOrder_FD;
SELECT @l_strRetVal = ''
SELECT @l_bFirstRow = 1
OPEN FoldRemarksCursor
FETCH FoldRemarksCursor INTO @l_strFoldRemark;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @l_bFirstRow = 1
BEGIN
SELECT @l_bFirstRow = 0
END
ELSE
BEGIN
SELECT @l_strRetVal = @l_strRetVal + (Char( 13 ))
END
SELECT @l_strRetVal = @l_strRetVal + COALESCE ( @l_strFoldRemark, '' )
FETCH FoldRemarksCursor INTO @l_strFoldRemark;
END
CLOSE FoldRemarksCursor
IF REPLACE( @l_strRetVal, CHAR ( 13 ), '' ) = ''
BEGIN
SELECT @l_strRetVal = ''
END
RETURN LTRIM( RTRIM( @l_strRetVal ))
END
****************
Bearing in mind i am a relative novice in this area please go easy on me!
Thanks,
James
November 17, 2006 at 6:40 am
Hi,
you can do this stuff without the cursor, if you want...
here is the modification
CREATE FUNCTION dba.spd_ListFoldRemarks (
@ArrivalPoint VARCHAR ( 4 ),
@DocType VARCHAR(30),
@VendID INTEGER )
RETURNS VARCHAR ( 8000 )
BEGIN
DECLARE @l_strRetVal VARCHAR ( 8000 )
--Arrival Point stuff
SET @l_strRetVal = ''
SELECT
@l_strRetVal=@l_strRetVal + CASE WHEN LEN(@l_strRetVal)> 0 THEN CHAR(13) ELSE '' END + IsNull(strRPrintingNote_FD,'')
FROM
DBA.Remarks_TB R
JOIN DBA.RemarkTypes_TB RT
ON RT.nRtRemarkTypeID_FD = R.nRtRemarkTypeID_FD
JOIN DBA.ArrivalPointAutoRemarks_TB AS APAR
ON R.nRRemarkID_FD = APAR.nRRemarkID_FD
WHERE APAR.strApArrivalPointCode_FD = @ArrivalPoint
AND APAR.strDtDocType_FD = @DocType
--Destination stuff
SELECT
@l_strRetVal=@l_strRetVal + CASE WHEN LEN(@l_strRetVal)> 0 THEN CHAR(13) ELSE '' END + IsNull(strRPrintingNote_FD,'')
FROM
DBA.Remarks_TB R
JOIN DBA.RemarkTypes_TB RT
ON RT.nRtRemarkTypeID_FD = R.nRtRemarkTypeID_FD
JOIN DBA.DestinationAutoRemarks_TB AS DAR
ON DAR.nRRemarkID_FD = R.nRRemarkID_FD
JOIN DBA.ArrivalPoints_TB AS AP
ON DAR.nDDestinationID_FD = AP.iDestinationID_FD
WHERE AP.strArrivalPointCode_FD = @ArrivalPoint
AND DAR.strDtDocType_FD = @DocType
--Country stuff
SELECT
@l_strRetVal=@l_strRetVal + CASE WHEN LEN(@l_strRetVal)> 0 THEN CHAR(13) ELSE '' END + IsNull(strRPrintingNote_FD,'')
FROM
DBA.Remarks_TB R
JOIN DBA.RemarkTypes_TB AS RT
ON R.nRtRemarkTypeID_FD = R.nRtRemarkTypeID_FD
JOIN DBA.CountryAutoRemarks_TB AS CAR
ON R.nRRemarkID_FD = CAR.nRRemarkID_FD
JOIN DBA.Destinations_TB AS D
ON D.strCountryCode_FD = CAR.strCCountryCode_FD
JOIN DBA.ArrivalPoints_TB AS Ap
ON D.iDestinationID_FD = Ap.iDestinationID_FD
WHERE Ap.strArrivalPointCode_FD = @ArrivalPoint
AND CAR.strDtDocType_FD = @DocType
--Vendor stuff
SELECT
@l_strRetVal=@l_strRetVal + CASE WHEN LEN(@l_strRetVal)> 0 THEN CHAR(13) ELSE '' END + IsNull(strRPrintingNote_FD,'')
FROM
DBA.Remarks_TB R
JOIN DBA.RemarkTypes_TB RT
ON R.nRtRemarkTypeID_FD = R.nRtRemarkTypeID_FD
JOIN DBA.VendorAutoRemarks_TB VAR
ON R.nRRemarkID_FD = VAR.nRRemarkID_FD
WHERE VAR.lVVendID_FD = @VendID
AND VAR.strDtDocType_FD = @DocType
ORDER BY nRtOrder_FD;
RETURN LTRIM( RTRIM( @l_strRetVal ))
END
cheers
November 17, 2006 at 10:10 am
Personaly, I would put this into a stored procedure. What are you going to do with the comma delimited list once it is returned? The reason I ask is that at some point, as your tables grow, your list has a good possibility to outgrow the varchar(8000) limit. So, what will you be using the data for and do you need it to be in a comma delimited list format?
November 17, 2006 at 11:14 am
Guys - thanks for your input so far!
What i really need this function/SP to do is list out the Remark Type (i.e. Insurance, Helath, Cancellation etc) then a carriage return and then the corresponding notes that relate to the remark type. If there is more than a single remark type then the function will carriage return agian and produce the next remark type, carriage return and remark notes etc. This is why i was using the UNION so the data would return something lke this:
Insurance
These are the terms of you insurance blah, blah, blah
Health
Health remarks blah, blah, blah
Cancellation
If flights are cancelled blah, blah, blah
These remarks will print on travel documentation when there is a remark that is assocaited with the destination or the arrival point, or the vendor or country. The management of the assocaition is handled in the UI by the client.
Hope this makes more sense. Let me know if you need me to clarify anything.
Thanks again!
James
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply