Have the following script. After checking it breaks on the function. Am I
invoking the function correctly in the script or not. Or is there something
else wrong with the script.
SELECT 'Dear ' + n.FirstName AS Salutation,
'RE: WHOLE HERD TB TEST FOR YOUR CATTLE HERD NUMBER ' + CONVERT(varchar,
n.HerdId) AS Regarding,
'This letter is to advise that the above cattle herd is due for a routine Tb
test on the following class of animals: ' + n.StockType + ' ' + 'and the
type of test is' + n.TestType + '.'AS FirstParagraph,
'You will be contacted by your Tb tester to arrange a suitable test date.'
AS SecondParagraph,
'Please telephone the Animal Health Board on 0800 4 TBINFO (0800 482 4636)
if you have any further inquiries. It will be helpful if you have this
letter to hand when you call. ' AS ThirdParagraph,
n.*
FROM (Select tr.Id as TestRequestId, p.FirstName, p.FullAddressName,
a.AddressLine1, a.AddressLine2, a.Town, a.PostCode, p.FullLegalName, h.Id AS
HerdID, ttli.Name AS TestType, stli.Name AS StockType
FROM dbo.TestRequest tr, Herd h, HerdTypeLookupItem htli,
SpeciesTypeLookupItem stli,
AnimalClassificationLookupItem acli, TestTypeLookupItem ttli,
StockTypeLookupItem stuli,
Person p, EpisodeTypeLookupItem etli, Address a
where h.Id = tr.HerdId
and htli.Id = h.HerdTypeLookupItemId
and stli.Id = htli.SpeciesTypeLookupItemId
and acli.Id = htli.SpeciesTypeLookupItemId
and ttli.Id = tr.TestTypeLookupItemId
and stuli.Id = tr.StockTypeLookupItemId
and p.Id = dbo.GetHerdContact
(tr.Id,'T',cast(convert(varchar(10),getdate(),101) as datetime))
and etli.Id = tr.EpisodeTypeLookupItemId
and a.ParentObjectId = p.Id AND a.ParentObjectTypeInd = 'P'
and (a.AddressTypeLookupItemId = 1)
and (a.DeletedDate IS NULL)
and (tr.Id = @testRequestId)
