question

tallg avatar image
tallg asked

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-2005user-defined-functiontable-valued
2 comments
10 |1200

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

Many thanks for your reply.
0 Likes 0 ·
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 Likes 0 ·
mjharper avatar image
mjharper answered
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

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

Cyborg avatar image
Cyborg answered
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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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