SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Accessing data returned from a table valued function


Accessing data returned from a table valued function

Author
Message
popq79 34287
popq79 34287
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26289 Visits: 17553
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 Modens 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)
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23699 Visits: 9730
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
popq79 34287
popq79 34287
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23699 Visits: 9730
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26289 Visits: 17553
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 Modens 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)
popq79 34287
popq79 34287
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 57
Many thanks and sorry about the earlier post.

Your methods works!


Thanks again for your help.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23699 Visits: 9730
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87022 Visits: 41112
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search