popq79 34287 (12/13/2012)

---

Hi All,

Is there a way to access values return from a table-valued function?

Sample code:

CREATE FUNCTION [dbo].[fn_MyFunction]

(@ID INT)

RETURNS @return_table TABLE

(Col1 int NULL,

Col2 int NULL)

AS

--- processing goes here...

INSERT @return_table

SELECT @col1_newvalue,@col2_newvalue

RETURN

Need to call above function from stored procedure as follows;

CREATE procedure MyProc

AS

SELECT col1, col2 from dbo.fn_MyFunction(1)

-- want to store return values for further processing by assigning them to local variables

declare @newvalue1 int

declare @newvalue2 int

set @newvalue1 = col1

set @newvalue2 = col2

--- start processing @newvalue1 and @newvalue2 further

Is this possible in SQL 2005?

Thanks in advance.