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))

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

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 ...

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'
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

select
 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