0

I've searched a lot, but still no chance on having a subquery to return multiple columns all at once. The following code works, but it sucks:

SELECT
    (SELECT Column1 FROM dbo.fnGetItemPath(ib.Id)) AS Col1,
    (SELECT Column2 FROM dbo.fnGetItemPath(ib.Id)) AS Col2,
    (SELECT Column3 FROM dbo.fnGetItemPath(ib.Id)) AS Col3
FROM ItemBase ib

I actually have got no idea how to pass ib.Id to the function and get the entire Column1, Column2, Column3 columns without calling the fnGetItemPath function 3 times.

Thanks in advance

Joe Bank
  • 653
  • 7
  • 20
  • Can you post the code for the function? If it's a table valued function then it should work, – Dan May 14 '12 at 06:55
  • Yes, it's a table-valued function. But the above SQL code really isn't efficient, since it has to run the function 3 different times separately, which is useless. I need a mechanism to run the function once to get the three columns all at once. Any idea? – Joe Bank May 14 '12 at 07:00
  • If its a table valued function then you don't have to do it as above, you can just SELECT * FROM dbo.fnGetItemPath(ib.Id)) – Dan May 14 '12 at 07:02
  • Also it looks like it isn't running the code 3 times but rather 3 times for each ID. Can you not rather write the fnGetItemPath() to output the full solution? i.e. get rid of the WHERE id = part of the function... – Dan May 14 '12 at 07:04

2 Answers2

4

You can move ti to "FROM" part and use outer apply (or cross apply).

check syntax yourself, but it should look something like this:

SELECT Column1, Column2, Column3
FROM ItemBase ib
Outer Apply dbo.fnGetItemPath(ib.Id)
Jānis
  • 2,216
  • 1
  • 17
  • 27
0

doesn't this work?

select 
     (select column1, column2, column3 from dbo.fnGetItemPath(ib.Id)) 
from ItemBase ib

or do you need something else?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amir.F
  • 1,911
  • 7
  • 29
  • 52
  • 2
    Nope, it doesn't. It fails since "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." – Joe Bank May 14 '12 at 07:05