Fetch result from function that returns table from query

Question

I would like to fetch the result set from a function for each row of a parent result. Here is table1:

column
------
a1
a2
a3
a4
a5

I would like to loop through table1 and get the result of the function for each item in the column. The function returns a table.

SELECT (SELECT * FROM functionReturnsTable(a.column))
FROM (SELECT column FROM table1) a

I have tried the query above however I get the error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

How can I write a query/function that will loop through table1 and combine the result of each item into a single table?

Aaron Bertrand · Accepted Answer · 2012-06-28 18:51:49Z

3

Maybe something like this:

SELECT * FROM dbo.table1 AS a
CROSS APPLY dbo.functionReturnsTable(a.column) AS f;

Though you probably wouldn't use SELECT * in the production version, and you would probably always use schema prefix and statement terminators, right? :-)

Also, please stop thinking about this as a "loop"...

answered Jun 28, 2012 at 18:51

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

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

1 Comment

Kermit Over a year ago

Based on your answer I'm using SELECT DISTINCT column, * at the moment. Let me try this out. Good point on the 'loop' mentality.

Collectives™ on Stack Overflow

Fetch result from function that returns table from query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related