SQL Server - selecting value from table value function in Stored Procedure

hi all

i have a table valued function which ll return a data

i want to select that in my SP

sample code
---------------
table valued function
----------------------------

ALTER FUNCTION [dbo].[fn_R_Current_MaxOcc1](@RLCID INT,@Date VARCHAR(50))

RETURNS TABLE
AS
RETURN
(

          SELECT  AVG(R_Current) as R_C FROM view_select_RCurrent_MaxOccurance r
          WHERE rc_cnt=(select max(rc_cnt) from view_select_RCurrent_MaxOccurance as r1 WHERE r.RLCID=r1.RLCID)
          AND r.RLCID=138 AND (convert(varchar(50),r.R_Date,101)='08/06/2011')
          GROUP By r.RLCID,r.rc_cnt,r.R_Date
)
GO


stored procedure
---------------------
select ID,Date,Desp from SampleTable

---------------------------------------------------------------------------------------------------------------------------------

i tried like this  in SP

select
 ID,Date,Desp,
(select Current  from dbo.fn_R_Current_MaxOcc1 (138,'08/06/2011')) as Current
 from SampleTable


but showing error
when tried simply like
select * from dbo.fn_R_Current_MaxOcc1 (138,'08/06/2011')
it was working

i need help for this


thanks in advance
MUTHU

you can't do like that.. you need write your table value function as Join in the Query..

see below ...

select
tblST.ID,
tblST.Date,
tblST.Desp, 
tblFN.R_C As Current
 from SampleTable tblST
INNER JOIN dbo.fn_R_Current_MaxOcc1 tblFN ON tblST.ID = tblFN.ID
Where
tblFN.RLCID = 138
tblFN.Date = '08/06/2011'

Actually you are writing wrong query, you can call table value function in select statement in that way because restult set return by TVF can't consider as column in another select statement as you are using.