Returning string from a function

Last Post 16 Aug 2010 01:36 PM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
werice
New Member
New Member

--
16 Aug 2010 12:06 PM
I have the function below:

ALTER FUNCTION [core].[f_GetContactSites]
(
@iContactID INT
)
RETURNS VARCHAR(1000)

AS BEGIN 
   DECLARE @vContactSites VARCHAR(500), @vSite VARCHAR(500)    
   SET @vContactSites = '' 
   DECLARE contactsites cursor forward_only for 
      SELECT DISTINCT site_name from core.org_site_contact osc 
      INNER JOIN core.org_site os ON os.org_site_id = osc.org_site_id 
      WHERE osc.contact_id = @iContactID

OPEN contactsites 
   FETCH NEXT from contactsites into @vSite 
   while @@fetch_status = 0 
   begin 
   SET @vContactSites = @vContactSites + ',' + @vSite 

   FETCH NEXT from contactsites into @vSite END CLOSE contactsites

DEALLOCATE contactsites
RETURN RIGHT(@vContactSites,LEN(@vContactSites)-1)
END

--The function works fine
The As I pass only ID to the function

The issue is I have code that calls the function and may have tons of ID's
As below:



SELECT c.Contact_ID, 
   c.First_Name, 
   c.Last_Name, 
   c.Phone_No, 
   c.Email_Address,
    '1/1/2008' as Agency_Hire_date,
   --osc.Agency_Hire_date, 
   c.Date_Of_Birth, 
   core.NORMALIIZED_LEVENSHTEIN(c.First_Name,@iFirst_Name) as Name_Score_11, 
   core.NORMALIIZED_LEVENSHTEIN(c.Last_Name,@iLast_Name) as Name_Score_22, 
   core.NORMALIIZED_LEVENSHTEIN(c.Date_of_Birth,@iDate_Of_Birth) as DOB_Score_23, 
   osc.Org_Site_ID, 
   os.Org_ID AS Org_ID_OS, 
   oc.Org_ID AS Org_ID_Org, 
   core.f_GetContactSites(c.Contact_ID) -- This calls the scalar function with the contact_id passed to it
    FROM core.CONTACT c 
      LEFT OUTER JOIN core.ORG_SITE_CONTACT osc 
      ON osc.Contact_ID = c.Contact_ID 
      LEFT OUTER JOIN core.ORG_CONTACT oc 
      ON oc.Contact_ID = c.Contact_ID 
      LEFT OUTER JOIN core.CLIENT_CAREGIVER cc 
      ON cc.Contact_ID = c.Contact_ID 
      LEFT OUTER JOIN core.ORG_SITE os 
      ON os.Org_Site_ID = osc.Org_Site_ID 
      WHERE (c.First_Name = @iFirst_Name OR 
      c.Last_Name = @iLast_Name OR 
      c.Date_Of_Birth = @iDate_Of_Birth


As you can see, I call the function with the contact_ID of the select statement "Can you do this?"
I need to capture the returned string through and pass it to the program.
Is there a better way of getting the string.

I get the error: Invalid length parameter passed to the RIGHT function.


gunneyk
New Member
New Member

--
16 Aug 2010 01:36 PM
If there are no rows returned from the cursor you will get the error. Try this instead:

DEALLOCATE contactsites

IF LEN(@vContactSites) >0
SET @vContactSites = RIGHT(@vContactSites,LEN(@vContactSites)-1)

RETURN @vContactSites

END
You are not authorized to post a reply.

Acceptable Use Policy