Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2006
    Posts
    15

    Unanswered: return multiple values from a function

    searched all over, couldn't find a solid answer...is it possible to return multiple values from a sql function with sql server 2005?

    e.g., I want to do this:

    select id, data, whatever, dbo.fnMyFunction(id, whatever) from table

    and have the output have columns (id, data, whatever, col1, col2) where col1 and col2 are returned by fnMyFunction

    possible? easier way? thanks in advance...

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    i guess returning multiple columns is not possible.....however if you write 2 functions for the two columns then it could be achieved something like

    select id, data, whatever, dbo.fnMyFunction1(id, whatever), dbo.fnMyFunction2(id, whatever) from table

  3. #3
    Join Date
    Oct 2006
    Posts
    15
    yeah, I was going to try that, but I thought it might be a performance drag since it's a fairly large amount of data I'll be returning. I'll try it anyway I guess, I shouldn't pre-judge sql server's optimization abilities

  4. #4
    Join Date
    May 2007
    Posts
    49
    Function can return multiple values, Check Table-Valued functions in help.

    Only think is you need to use it in where clause, something like :

    Select
    Yourtable.id, Yourtable.data, Yourtable.whatever,
    fnTable.col1, fnTable.col2
    from Yourtable,
    (Select * from dbo.fnMyFunction(id, whatever) ) as fnTable
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •