Call Table Valued Function in Stored Procedure

Question

Hi,

I have created one table-valued function. And I want to call this function in stored procedure.

How can I achieve this?

I want to select  billid and visitid into SP.

 

Function query is as below: 

 

CREATE FUNCTION funReturnIPDData

(

)

RETURNS TABLE

AS

RETURN

(

-- Add the SELECT statement with parameter references here

with cte_billinfo(bill_id, visit_id, totalbill) as

(

select B.ipdbill_id, B.ipdpatvisit_id, B.ipdbill_grandtotal from Tbl_IPDBillingDetails B, Tbl_IPDPatientVisit V

where B.ipdpatvisit_id=V.ipdpatvisit_id

),

cte_billselect(billid, visitid)

as

(

select max(bill_id) as billid, visit_id from cte_billinfo c, Tbl_IPDPatientVisit V

group by c.visit_id, V.ipdpatvisit_id, c.totalbill

having c.visit_id=V.ipdpatvisit_id

)

select billid,visitid from cte_billselect

)

GO