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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ben Durkin
  • 429
  • 1
  • 6
  • 20

1 Answers1

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

Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • (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) – Stavros Zotalis Apr 28 '15 at 21:37
  • How is it redundant? It returns set. Try in fiddle – Giorgi Nakeuri Apr 28 '15 at 21:42
  • 1
    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. – Stavros Zotalis Apr 28 '15 at 21:44