accessing returned values of table-valued UDF

Question

question

tallg asked Dec 13, '12

accessing returned values of table-valued UDF

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.

sql-server-2005 user-defined-function table-valued

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tallg commented · Dec 13, 2012 at 07:33 PM

Many thanks for your reply.

0 ·

JohnM commented · Dec 13, 2012 at 08:34 PM

If you found one of the answers useful, please return the favor by accepting one of them as the answer. This will also help out future users.

0 ·

Answer 1 · 2012-12-13T17:25:21Z

mjharper answered Dec 13, '12

You should be able to do it like this: SELECT @newvalue1 = col1, @newvalue2 = col2 from dbo.fn_MyFunction(1) That assumes your function only returns one row of course.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2012-12-13T17:27:42Z

Cyborg answered Dec 13, '12

Declare @NewValue1 INT, @NewValue2 INT


SELECT @NewValue1 = Col1, @NewValue2 = Col2
FROM from dbo.fn_MyFunction(1)

Above query works well if your function returns only one row, it will not be accurate if your function returns more than one row. In that case you should put your result to a temp table and process each rows using while loop as shown below.


DECLARE @temp  table (ID INT IDENTITY, NewValue1 INT, NewValue2 INT)
DECLARE @MaxRows INT,
        @ID INT = 1,
        @NewValue1 INT,
        @NewValue2 INT

INSERT INTO @temp
SELECT Col1, Col2 FROM dbo.fn_MyFunction(1)

SELECT @MaxRows = MAX(ID) FROM @temp
WHILE (@ID <= @MaxID)
BEGIN

SEECT @NewValue1 = NewValue1, @NewValue2 = NewValue2 FROM @temp WHERE ID = @ID

SET @ID = @ID+1
END

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question