Function returning multiple values?

Question

Function returning multiple values?

Denby

Hall of Fame

Points: 3120
More actions
May 2, 2007 at 9:53 am

#177784

Hi guys, is it possible to create a single function that returns multiple value's?
if possible can anyone share the syntax doing a similar task.
ex. I a table with records of multiple occurences linked to a single primary key in the parent table.
i want to create a select query within a function that is able to concatenate multi values in each column which is then returned to the call function.

thx in advance for any help/suggestions

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

But according to this:

"i want to create a select query within a function that is able to concatenate multi values in each column which is then returned to the call function"

you actually need single output value function.

Can you explain more explicitly?

_____________
Code for TallyGenerator

Denby Hall of Fame Points: 3120 More actions · Answer 2

ooh sorry, what i'm actaully saying is that i want the function to return multiple columns (Multiple output value function).

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Still not clear what are you after.

You may use table function (you may find in amongst function templates in Query Analyzer).

But I would suggest to consider using a view instead.

Sorry, I need more information to be more specific.

_____________
Code for TallyGenerator

asiaindian SSCrazy Points: 2493 More actions · Answer 4

you could insert your values into a table and return the table.

declare a table variable in the function, insert ur multiple values into that table variable and return thetable variable.

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 5

If we are talking text data and you want to display the result in multiple lines you might add a char(10)+char(13) to each result, and concatenate everything into one big string.

In a web page or excel-cell this will display as several lines...

regards

karl

Best regards
karl

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 6

PLEASE... take the time to show some data before and after the function gets done with it... it sounds like you want a table function but we can't tell for sure.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ram-425798 Right there with Babe Points: 798 More actions · Answer 7

CREATE FUNCTION dbo.fnTest (@RunDate datetime)

RETURNS @retTable TABLE

(

col1 int,

col2 varchar(10),

col3 varchar(40),

..

)

AS

--

Begin

INSERT INTO @retTable(col1, col2, col3,..)

SELECT val1, val2, val3,..

FROM table1

--

RETURN

END

Hope this helps.....

Denby Hall of Fame Points: 3120 More actions · Answer 8

Denby

Hall of Fame

Points: 3120

May 7, 2007 at 9:30 am

#704679

Kewl, thx swamy