-- You might want to configure that function as an iTVF for performance:

CREATE FUNCTION [dbo].[iTVF_CallbackService_ufn_GetCallerData]

(

@nCaller_NoArgINTEGER,

@cCallerLangArgCHAR(1)

)

RETURNS TABLE

AS

RETURN (

SELECT

[Caller_No]= @nCaller_NoArg,

[Clientname]= RTRIM(LTRIM(c.First_Name)) + ' ' + RTRIM(LTRIM(c.Last_Name)),

[ClientLanguage]= CASE

WHEN @cCallerLangArg = 'F'

THEN UPPER(RTRIM(LTRIM(l.Descript_f)))

ELSE UPPER(RTRIM(LTRIM(l.Descript)))

END,

[ClientOrganization]= ISNULL(RTRIM(LTRIM(c.Organization)), ''),

[ClientAddressLine1]= ISNULL(RTRIM(LTRIM(c.Street)), ''),

[ClientAddressLine2]= ISNULL(RTRIM(LTRIM(c.City)) + ', ' + CASE

WHEN @cCallerLangArg = 'F'

THEN UPPER(RTRIM(LTRIM(c.ps_prov_code))) -- c.ps_prov_code_f???

ELSE UPPER(RTRIM(LTRIM(c.ps_prov_code)))

END + ' ' +

RTRIM(LTRIM(CASE

WHEN c.Postcode LIKE '[A-Z][0-9][A-Z][0-9][A-Z][0-9]'

THEN LEFT(c.PostCode, 3) + ' ' + RIGHT(c.PostCode, 3)

ELSE c.PostCode

END)), ''),

[ClientAddressLine3]= ISNULL(CASE

WHEN @cCallerLangArg = 'F'

THEN UPPER(RTRIM(LTRIM(s_country_name_f)))

ELSE UPPER(RTRIM(LTRIM(s_country_name)))

END, ''),

[ClientTelephone1]= ISNULL(RTRIM(LTRIM(c.Telephone)), ''),

[ClientTelephone1Ext]= ISNULL(RTRIM(LTRIM(c.Extension)), ''),

[ClientTelephone2]= ISNULL(RTRIM(LTRIM(c.Night_Phone)), ''),

[ClientTelephone2Ext]= '',

[ClientEmail]= ISNULL(RTRIM(LTRIM(c.s_Email)), '')

FROM dbo.[Caller] c

CROSS JOIN dbo.[Lookup] l

LEFT OUTER JOINdbo.Province p

ON p.ps_Prov_Code = c.ps_Prov_Code

LEFT OUTER JOINdbo.Countryct

ON ct.ps_country_code = c.Country

WHERE c.Caller_No = @nCaller_NoArg

AND l.lookup_ch = @cCallerLangArg

AND l.Item_name = 'Language'

)