1

Is there any way to return multiple values from a function? Ideally looking to do something along these lines (I know that syntax is wrong!)

SELECT 
    1 as test1, 2 as test2, 3 as test3, 
    fn_result(test1, test2, test3)[0] as result1, 
    fn_result(test1, test2, test3)[1] as result2, 
    fn_result(test1, test2, test3)[2] as result3

FUNCTION fn_result(val1, val2, val3) 
   RETURN (val1 + val2, val2 + val3, val1 + val3)

I have no experience at all with SQL functions so not sure if such a thing can be done.

Thanks

Improve this question
2
  • 1
    You want a table-valued function Commented Apr 28, 2015 at 21:22
  • well you could return a table-valued function, or an XML document, but you're using a scalar-valued function syntax. Commented Apr 28, 2015 at 21:22

1 Answer 1

Reset to default
3

Use inline table valued function with cross apply:

Create function fn_result(@val1 int, @val2 int, @val3 int)
returns table
as
Return (select @val1+@val2 as val12,
@val1+@val3 as val13, @val2+@val3 as val23)
go

select * from (select 1 as test1, 2 as test2, 3 as test3) t
cross apply dbo.fn_result(t.test1, t.test2, t.test3)

Output:

test1   test2   test3   val12   val13   val23
1       2       3       3       4       5

Fiddle http://sqlfiddle.com/#!6/857c1/3

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

(select * from dbo.fn_result(t.test1, t.test2, t.test3)) is redundant. I would use only dbo.fn_result(t.test1, t.test2, t.test3)
How is it redundant? It returns set. Try in fiddle
It is redundant because you can write the same query like this: select * from (select 1 as test1, 2 as test2, 3 as test3) t cross apply dbo.fn_result(t.test1, t.test2, t.test3) ca Imo it is cleaner with one less level of nesting which does not offer anything.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.