December 13, 2012 at 9:23 am
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.
December 13, 2012 at 11:03 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2012 at 11:07 am
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
December 13, 2012 at 12:18 pm
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.
December 13, 2012 at 12:24 pm
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
December 13, 2012 at 12:29 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2012 at 12:33 pm
Many thanks and sorry about the earlier post.
Your methods works!
Thanks again for your help.
December 13, 2012 at 1:13 pm
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
December 13, 2012 at 7:52 pm
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
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply