return 4 output from a function in sqlserver

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

Other SQL Server Topics (2005)

return 4 output from a function in sqlserver

Author

Topic

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-06-16 : 06:15:59

Hi I need a function which gets 3 parameters and input and returns 4 integers as output.
I don't know how to return 4 integers as output
any idea is appreciated.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-16 : 06:31:06

Create an inline table valued function similar to following example:

-- create function
create function dbo.test_2(@p1 int, @p2 int, @p3 int)
returns table
as
return (select @p1 as p1, @p2  as p2, @p3  as p3, (@p1+ @p2+ @p3) as total )
GO

-- Execute and test function
select * from dbo.test_2(1,2,3)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-06-16 : 06:38:29

Wow thanks for the immediate response!

Now I'm having a more complex query; that is, I have 4 functions (each returns a table) and I'd like to have one select from the 4 functions but if I write: select * from func1(), func2(), ... it returns a Cartesian product of the result of functions, since I just want to put the result of the functions beside each other in one row
Do you have any idea?
Hope I've explained my question clearly
suppose func1 returns a1,a2, a3,
func2 returns b1, b2, b3, b4
....
I need a result of a1, a2, a3, b1, b2, b3, b4, ...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-16 : 06:42:15

Is there any common column (key column) among the resultset of those four functions?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-06-16 : 06:43:48

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-16 : 06:48:53

Then I don't think you will be able to achieve the desired output without cartesian product.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-06-16 : 06:53:21

ok knowing that it's not possible is also a good point then I'll gather the 4 functions in one function and use that in the select
Thanks a million Harsh :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-16 : 06:59:31

If each and one function only returns one record with n columns, the cartesian product will also contain only 1 record and many many columns.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources