x

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.

more ▼

asked Dec 13, 2012 at 05:18 PM in Default

avatar image

tallg
30 1 1 1

Many thanks for your reply.

Dec 13, 2012 at 07:33 PM tallg

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.

Dec 13, 2012 at 08:34 PM JohnM
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Dec 13, 2012 at 05:25 PM

avatar image

mjharper
2.2k 3 8 14

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Dec 13, 2012 at 05:27 PM

avatar image

Cyborg
10.8k 37 55 51

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2030
x29
x19

asked: Dec 13, 2012 at 05:18 PM

Seen: 832 times

Last Updated: Dec 16, 2012 at 02:48 AM

Copyright 2017 Redgate Software. Privacy Policy