Removing User Defined Functions and SQL Profiler Question

[syukna]

I have a stored procedure that is currently utilizing a user defined function. I need to use these functions because I'm returning one row with general info (IE: Name, Title, etc), and within that same row I return all the fiscal years associated to the item as a single field in the result set. For example I would create the following SQL statement that calls the function below it. So for one row the data could look like "John Smith, Senior Developer, FY08 FY09 FY10". The function essentially concatenates multiple rows and returns a single VARCHAR with all of the FY's. Is it possible to acheive the desired functionality without using a User Defined Function here? This leads me into my next question. I notice in the SQL Profiler that any stored procedure that contains a User Defined Function actual runs the Select statement more than once.

What I mean is for example say the SELECT statment below returns one row. The particular result has 3 Fy's associated to (John Smith, Senior Developer, FY08 FY09 FY10). In the SQL Profiler I would see the select statement listed 3 times, one time for ever fiscal year. The only event I am capturing is "SP:Starting". So my profiler window will look similar to the following:

SP:Starting SELECT Name, Title, dbo.fnGetFiscalYears(ID) AS 'Fiscal Year' .....
SP:Starting SELECT Name, Title, dbo.fnGetFiscalYears(ID) AS 'Fiscal Year' .....
SP:Starting SELECT Name, Title, dbo.fnGetFiscalYears(ID) AS 'Fiscal Year' .....

SELECT Name, Title, dbo.fnGetFiscalYears(ID) AS 'Fiscal Year'
FROM myTable

1 CREATE FUNCTION fnGetFiscalYears
2 (
3 @ItemID int
4 )
5 RETURNS VARCHAR(100)
6 AS
7 BEGIN
8 DECLARE @FY VARCHAR(100),@sep VARCHAR(3)
9 SET @sep=', '
10 --COALESCE all the FY's together to return them as
11 --a single VARCHAR
12 SELECT @FY = COALESCE(@FY + @sep,'') + 'FY' +
13 CONVERT(VARCHAR(2), FYID)
14 --FYID is an INT datatype corresponding to the last two digits of a year (EX: 8)
15 FROM tblItemsToFiscalYears
16 WHERE ItemID = @ItemID
17
18 RETURN @FY
19 END
20
21

I appreciate ANY help one may be able to give me...