select查询调用子查询oracle,t-sql选择查询，子查询/ proc调用子查询中有2个或更多列(t-sql select query with sub-query/proc call wit...

I have written quite a complex t-sql query (by my standards as I'm not a DBA) which has an even more complex sub-query within it that I abstracted as a scalar function to calculate and retrieve a single value. It was all working well until I realised I needed the data from a second column in the sub-query/function and now I am stuck. Obviously I can convert the function to a proc to return multiple values (or alternatively declare the sub-query directly within the main query) but how can I get them neatly into the parent select query as individual columns?

I know I can do some clunky things like concatenate (comma delimit) the results or use a FOR XML (which my query includes for another sub-query that is returning the *..1 data from another table) but I am using the Entity Framework and would prefer to keep these two particular values strongly typed. My Googling keeps turning up the use of EXISTS but I don't see any example of where the columns are actually retrieved from the select query i.e. it's only used to apply logic to the where clause using multiple columns.

Is this just something that can't be done? My last resort is to attempt to combine the sub-query as more joins but I am trying to keep my query readable and combining them will throw that idea out the window.

Thanks in advance - "No this can't be done" is also an answer I am prepared to accept :-)

Here's a "novel" example using a scalar function which I want to change to a stored proc that returns 2 columns:

SELECT t1.Field1,

t2.Field2,

,dbo.fn_Select_ComplexStuff(t1.Field3) AS ComplexStuff

FROM MyTable1 AS t1

INNER JOIN MyTable2 AS t2 ON t1.id = t2.id

WHERE t1.Field4 = 'Albatross'