returning row from function

Is it possible to do something like this:
First create function(function returns only one row):
CREATE FUNCTION dbo.s_function(@.fID int)
RETURNS @.tbl TABLE (pName varchar(20),quantity decimal(15,5)) AS
BEGIN
INSERT INTO @.tbl
SELECT TOP 1 * from...
WHERE tID=@.fID
RETURN
END
Then use function like this:
select t.*,f.* FROM testTable t INNER JOIN dbo.s_function(t.tID)f ON 1=1
If I would need only one value, than there won't be a problem:
I would create a function which returns only one value and use it:
select *,retValue=dbo.s_function(t.tID)
FROM testTable t
Regards,Ssimon,

> Is it possible to do something like this:
This can not be done in SQL Server 2000. In version 2005, you can use "CROSS
APPLY" operator.
Using CROSS APPLY in SQL Server 2005
http://www.sqlteam.com/item.asp?ItemID=21502
AMB
"simon" wrote:

> Is it possible to do something like this:
> First create function(function returns only one row):
> CREATE FUNCTION dbo.s_function(@.fID int)
> RETURNS @.tbl TABLE (pName varchar(20),quantity decimal(15,5)) AS
> BEGIN
> INSERT INTO @.tbl
> SELECT TOP 1 * from...
> WHERE tID=@.fID
> RETURN
> END
>
> Then use function like this:
> select t.*,f.* FROM testTable t INNER JOIN dbo.s_function(t.tID)f ON 1=1
> If I would need only one value, than there won't be a problem:
> I would create a function which returns only one value and use it:
> select *,retValue=dbo.s_function(t.tID)
> FROM testTable t
> Regards,S
>
>|||
>select t.*,f.* FROM testTable t INNER JOIN dbo.s_function(t.tID)f ON 1=1
Don't believe you can do this in SQL Server 2000. For SQL Server 2005
you can use CROSS APPLY|||simon wrote:
> Is it possible to do something like this:
> First create function(function returns only one row):
> CREATE FUNCTION dbo.s_function(@.fID int)
> RETURNS @.tbl TABLE (pName varchar(20),quantity decimal(15,5)) AS
> BEGIN
> INSERT INTO @.tbl
> SELECT TOP 1 * from...
> WHERE tID=@.fID
> RETURN
> END
>
> Then use function like this:
> select t.*,f.* FROM testTable t INNER JOIN dbo.s_function(t.tID)f ON 1=1
> If I would need only one value, than there won't be a problem:
> I would create a function which returns only one value and use it:
> select *,retValue=dbo.s_function(t.tID)
> FROM testTable t
> Regards,S
In SQL Server 2005 you could use the OUTER APPLY operator. In your case
however that seems to be unnecessary. For instance you could perhaps
use a derived table instead:
SELECT T.*, G.*
FROM testTable AS T
JOIN
(SELECT MIN(key_col) AS key_col
FROM /* unspecified */
GROUP BY tID) AS F
ON F.tID = T.tID
JOIN /* unspecified */ AS G
ON F.key_col = G.key_col ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi David,
I had very simple question, so I didn't post all code with samples,
otherwise, I agree with you.
I can use derived tables - thank you, but it doesn't work fast enough.
The function is couple of times faster than join with derived table, when
you have a lot of data.
So I usualy use functions, which filters all rows only to those I need (on
the other hand derived table first insert all data into some internal temp
table and than filters it in join - I think)
The problem which I have is, that I can't include in resulting row more than
one data returned from function.
So I use function for each data, when I would like to get more than one
column from function:
SELECT t.*, columnF1=dbo.function1(t.ID),columnF2=dbo.function2(t.ID) from
table t ...
I wonder if it,s possible to include in result set more than one column
returned from function?
my function would return for example 3 data, so 3 columns - only one row if
we speak in table world.
Something like this would work if I had table(with one row) instead of
function:
select t.*,f.* FROM testTable t INNER JOIN testTable1 f ON 1=1
But if I have function,which returns table with one row, won't work.
select t.*,f.* FROM testTable t INNER JOIN dbo.s_function(t.tID)f ON 1=1
So, I guess that it's not possible.
Regards,Simon
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145282430.488437.92570@.g10g2000cwb.googlegroups.com...
> simon wrote:
> In SQL Server 2005 you could use the OUTER APPLY operator. In your case
> however that seems to be unnecessary. For instance you could perhaps
> use a derived table instead:
> SELECT T.*, G.*
> FROM testTable AS T
> JOIN
> (SELECT MIN(key_col) AS key_col
> FROM /* unspecified */
> GROUP BY tID) AS F
> ON F.tID = T.tID
> JOIN /* unspecified */ AS G
> ON F.key_col = G.key_col ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||simonZ wrote:
> I can use derived tables - thank you, but it doesn't work fast enough.
> The function is couple of times faster than join with derived table, when
> you have a lot of data.
How did you test that? Do you have a working solution using a function?
I'm actually surprised if a multi-statement table-valued function could
improve on a dervied table query for what you posted.

> SELECT t.*, columnF1=dbo.function1(t.ID),columnF2=dbo.function2(t.ID) from
> table t ...
> I wonder if it,s possible to include in result set more than one column
> returned from function?
Already answered. Not possible in 2000. CROSS APPLY / OUTER APPLY in
2005.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I have table with a lot of data. When I need one data, for example, a volume
on some location in warehouse, I have 2 possibilities:
first option with derived table:
SELECT t.*,T1.volume, FROM table t1
INNER JOIN (SELECT wID,volume=sum(w.volume) from .......) as T1 ON
t.wID=T1.wID
WHERE ....
second option with function:
SELECT t.*,volume=dbo.functionV(wID) FROM table t1
and function:
set @.returnValue=(SELECT sum(volume) from ...
WHERE wID=@.wID)
Here, the function filters all rows to the single wID and calculate volume
only for this wID.
In derived table, I get all volumes for all wIDs, and than it's filtered to
my wID with join condition: ON t.wID=T1.wID
That is the reason, that function is much faster than derived table - I
guess.
My examples are more complicated, but it's the same idea. I have many cases,
where I create a function instead of derived table just because of
performance.
Even when I need more than one data (volume, mass,...), which can all be
calculated with single query in derived table, it's faster to create a
function for each column withs it's own query.
Am I doing something wrong?
Another question?
A while ago I posted one example but nobody answer me.
Can you look at it?
Just run the code and you will see, what is the point, otherwise I can
explain to you in more details.
The example works correct but it uses 2 cursors, which has bad performance.
Can you do the query which will return the same results as this example but
without 2 cursors?
http://groups.google.com/group/micr...2252c36ea4152d5
This example is not real one, it's simplified, but it serves as problem
review.
Thanks,
Simon
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145358771.297709.215400@.t31g2000cwb.googlegroups.com...
> simonZ wrote:
> How did you test that? Do you have a working solution using a function?
> I'm actually surprised if a multi-statement table-valued function could
> improve on a dervied table query for what you posted.
>
> Already answered. Not possible in 2000. CROSS APPLY / OUTER APPLY in
> 2005.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>