accessing returned values of table-valued UDF

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

accessing returned values of table-valued UDF

Author

Topic

tallg
Starting Member

5 Posts

Posted - 2012-12-13 : 11:19:45

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.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-13 : 11:23:27

try

SELECT @newvalue1 = col1, @newvalue2 = col2 from dbo.fn_MyFunction(1)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tallg
Starting Member

5 Posts

Posted - 2012-12-13 : 11:27:53

Thanks but already tried that.

Error message as follows

"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-13 : 19:55:34

quote:

Originally posted by tallg

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.

You must declare inside the function for @col1_newvalue,@col2_newvalue and theirs value

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 03:26:51

you should access table values UDF just like a table

ie


...
 declare @newvalue1 int
  declare @newvalue2 int

SELECT @newvalue1 = col1, 
@newvalue2 = col2 
from dbo.fn_MyFunction(1)  
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-14 : 05:32:12

quote:
Originally posted by tallg

Thanks but already tried that.

Error message as follows

"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

Not sure why - this works on my system but I'm v2012
CREATE FUNCTION [dbo].test
(@ID INT)
RETURNS @return_table TABLE
(Col1 int NULL,
Col2 int NULL)
AS
begin
INSERT @return_table
SELECT 1,2
RETURN
end
go
declare @newvalue1 int
declare @newvalue2 int
SELECT @newvalue1 = col1, @newvalue2 = col2 from dbo.test(1)
select @newvalue1, @newvalue2

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 05:34:35

quote:
Originally posted by tallg

Thanks but already tried that.

Error message as follows

"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

that means you're trying to return some other column value also in select without assigning it to variable. In that case you cant combine assignment and retrieval query inside same select

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources