SQL Server '05: SP and UDF performance problem

Hi,

I have a UDF which on it's own works well but when I call it within the SP below the the query hangs (should return 1 or two rows, table contains about 50,000 rows)

Here is my UDF -

Code:

---

CREATE FUNCTION  uf_gGetPerson_Phone(
        @Jnl_Code AS VarChar(10),
        @User_Id AS numeric,
        @Phone_Type AS VarChar(100)
)
RETURNS VarChar(255)
with schemabinding
AS
BEGIN
        DECLARE @Detail varchar(255)
        SET @Detail=(
                                        SELECT TOP 1 ph.Detail
                                        FROM Sirius.phone ph
                                        LEFT OUTER JOIN Sirius.Phone_Jnl pj ON ph.Phone_Id=pj.Phone_Id AND pj.Status='A' AND pj.Jnl_Code=@Jnl_Code
                                        WHERE ph.User_Id=@User_Id
                                        AND ph.Phone_Type=@Phone_Type
                                        AND ph.Status='A'
                                        ORDER BY CASE pj.Jnl_Code WHEN @Jnl_Code THEN 0 ELSE 1 END ASC, ph.Default_Opt DESC, ph.Sort_Order, ph.Phone_Id
                                )
        RETURN @Detail
END

---

Here is my SP; I've hilighted the problem line, When I replace the call to the UDF (sirius.uf_gGetPerson_Phone) with the actual query from the UDF my SP executes immediately -

Code:

---

CREATE PROCEDURE up_gGetNewAuthors(
  @MS_No varchar(15),
  @Version_No smallint,
  @Submitting_Author numeric=null
)
As

SET NOCOUNT ON

DECLARE @Jnl_Code varchar(10)

IF @Submitting_Author Is NULL BEGIN
        SELECT @Jnl_Code=Jnl_Code, @Submitting_Author=Submit_Auth_ID FROM MS_Version WHERE MS_No=@MS_No AND Version_No=@Version_No
END ELSE BEGIN
        SET @Jnl_Code=(SELECT Jnl_Code FROM Manuscript WHERE MS_No=@MS_No)
END
--  Print 'Submitting Author ID: '+Convert(varchar,@Submitting_Author)

SELECT
        a.Author_ID
        ,p.User_ID
        ,p.Prefix
        ,p.First_Name
        ,p.Last_Name
        ,p.Suffix
        ,p.User_Name
        ,sirius.uf_gGetPerson_Phone(@Jnl_Code,p.User_Id,'Email') As Email
        ,(SELECT Title FROM MS_Version WHERE MS_No=@MS_No AND Version_No=@Version_No) As Paper_Title
        ,(SELECT Count(ph.Phone_Id) FROM People p2 JOIN Phone ph ON p2.User_Id=ph.User_Id AND ph.Status='A' AND ph.Phone_Type='Email' WHERE ph.Detail=sirius.uf_gGetPerson_Phone(@Jnl_Code,p.User_Id,'Email') AND p2.Status NOT IN('T') AND p2.Deleted=0) As Active_Email_Count       
FROM People p
JOIN Authors a ON p.User_ID=a.User_ID
WHERE a.MS_No=@MS_No
AND a.Version_No=@Version_No
AND a.Deleted=0
AND p.Deleted=0
AND p.Status IN('T')
AND p.Owner=@Submitting_Author
ORDER BY a.Sort_Order, a.Author_ID


SET NOCOUNT OFF

Return @@Error
GO

---

Here is the problem subquery clipped out -

Code:

---

declare @User_Id int                set @User_Id=1
declare @Jnl_Code varchar(10)        set @Jnl_Code='BJ'

SELECT Count(ph.Phone_Id)
FROM People p2
JOIN Phone ph ON p2.User_Id=ph.User_Id
AND ph.Status='A'
AND ph.Phone_Type='Email'
WHERE ph.Detail=sirius.uf_gGetPerson_phone(@Jnl_Code,@User_Id,'Email')
AND (p2.Status < 'T' OR p2.Status > 'T')
AND p2.Deleted=0

---

Any help with this will be greatly appreciated, I'd be happy to restructure my query if it's going to improve the performance

Cheers Al