Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Accessing data returned from a table valued function Expand / Collapse
Author
Message
Posted Thursday, December 13, 2012 9:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 6:44 AM
Points: 7, Visits: 57
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.
Post #1396250
Posted Thursday, December 13, 2012 11:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
Yes you can do this quite easily.

select col1, col2 
into #temp
from dbo.fn_MyFunction(1)

select * from #temp

I would suggest that you look at your function more closely. You have declared your function as multi-line table function. These will not perform nearly as well as an iTVF (inline Table Valued Function).

Also it is against current best practices to prefix your functions with fn_.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1396314
Posted Thursday, December 13, 2012 11:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 13,872, Visits: 9,600
Yes, you can do that, but you need to plan for what to do if the function returns more than 1 row, or make absolutely sure it can never do that.

declare @var1 int, @var2 int;

select @var1 = col1, @var2 = col2
from dbo.MyFunction(input);

Looks like that.

But, keep in mind, if the UDF returns more than 1 row, you can't be sure which row the variables will get their values from. If you need to be sure, then there are ways to handle that.

Also keep in mind, table-value UDFs are often a negative on SQL performance. Depends on how they're built, of course, but it's frequently something to move away from on busy systems or in performance-sensitive stored procedures.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1396318
Posted Thursday, December 13, 2012 12:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 6:44 AM
Points: 7, Visits: 57
Thank you for your replies.

Have already tried assigning local variables method by GSquared but returned the following error:

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

I prefer to use this method, but if no suggestions to resolve this error, will try the temp table method by SeanLange.
Post #1396340
Posted Thursday, December 13, 2012 12:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 13,872, Visits: 9,600
I just tested this, and it worked:

USE ProofOfConcept;
GO
CREATE FUNCTION dbo.MyFunction (@Input INT)
RETURNS TABLE
AS
RETURN (SELECT @Input AS C1, @Input+1 AS C2);
GO
DECLARE @Var1 INT, @Var2 INT;

SELECT @Var1 = C1, @Var2 = C2
FROM dbo.MyFunction(1);

SELECT @Var1, @Var2;
GO
DROP FUNCTION dbo.MyFunction;

I also tried this:

USE ProofOfConcept;
GO
CREATE FUNCTION dbo.MyFunction (@Input INT)
RETURNS @T TABLE (C1 INT, C2 INT)
AS
BEGIN
INSERT INTO @T (C1, C2)
VALUES (@Input, @Input+1);
RETURN;
END;
GO
DECLARE @Var1 INT, @Var2 INT;

SELECT @Var1 = C1, @Var2 = C2
FROM dbo.MyFunction(1);

SELECT @Var1, @Var2;
GO
DROP FUNCTION dbo.MyFunction;

@@VERSION =
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

ProofOfConcept database in Compatibility 100.

What precisely are you doing? It should work, if it follows the pattern above.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1396344
Posted Thursday, December 13, 2012 12:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
This works for me.

create FUNCTION [dbo].[fn_MyFunction] 
(@ID INT)
RETURNS @return_table TABLE
(Col1 int NULL,
Col2 int NULL)
AS begin
--- processing goes here...
INSERT @return_table
SELECT 1, 2 union all
select 55, 73
RETURN

end

go

declare @var1 int, @var2 int;

select @var1 = col1, @var2 = col2
from dbo.fn_MyFunction(1)

select @var1, @var2


However the reason I suggested using a temp table is because you want to capture all the rows not just one. If you don't need multiple rows you should consider a complete rethinking of what you are doing. For that type of situation it would be better to use a stored proc with multiple output parameters instead of using a multi statement table valued function.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1396346
Posted Thursday, December 13, 2012 12:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 6:44 AM
Points: 7, Visits: 57
Many thanks and sorry about the earlier post.

Your methods works!


Thanks again for your help.
Post #1396351
Posted Thursday, December 13, 2012 1:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 13,872, Visits: 9,600
Sean Lange (12/13/2012)
This works for me.

...

However the reason I suggested using a temp table is because you want to capture all the rows not just one. If you don't need multiple rows you should consider a complete rethinking of what you are doing. For that type of situation it would be better to use a stored proc with multiple output parameters instead of using a multi statement table valued function.


Yep.

That's also why I brought up (a) dealing with multiple rows, and (b) performance concerns with table-value UDFs.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1396365
Posted Thursday, December 13, 2012 7:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
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.


Check under CREATE FUNCTION in Books Online. If you can write the code as a single query, the you can make this function an "INLINE" Table Valued function which will be MUCH faster than a "MULTI-LINE" Table Valued function, which is what you have right now.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1396455
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse