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

Asked By muthuraman alexander on 29-Sep-11 05:17 AM
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))


          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

stored procedure
select ID,Date,Desp from SampleTable


i tried like this  in SP

(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

Rohan Dave replied to muthuraman alexander on 29-Sep-11 05:44 AM
you can't do like that.. you need write your table value function as Join in the Query..

see below ...

tblFN.R_C As Current
 from SampleTable tblST
INNER JOIN dbo.fn_R_Current_MaxOcc1 tblFN ON tblST.ID = tblFN.ID
tblFN.RLCID = 138
tblFN.Date = '08/06/2011'
Web Star replied to muthuraman alexander on 29-Sep-11 05:47 AM
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.

If you want to get some column or all column from that TVF in your select statement than you need to use This TVF result set as table and join on some condition than you will get restuls

try like this

 SampleTable.ID,SampleTable.Date,SampleTable.Desp, Current.col1, Current.col2 From 
(select Current  from dbo.fn_R_Current_MaxOcc1 (138,'08/06/2011')) as Current 
 Inner join SampleTable On SampleTable.id = Current.id