Create a concatenated table from another table using function

Question

I have a function GetChildren(@id uniqueidentifier) that returns a table of nodes and a table of parents deleted from delete trigger I want to make a big table of nodes concatenating output of GetChildren for each row.Id of deleted, how do I do that? Please, say how to make it without variables and loops in only pure sql-way

I want something similar to SelectMany from linq c#

example:

deleted table

+-----+
| Id  |
+-----+
| 111 |
+-----+
| 222 |
+-----+

Nodes table

+-----+----------+
| Id  | ParentId |
+-----+----------+
| 111 | ...      |
+-----+----------+
| 222 | ...      |
+-----+----------+
| 333 | ...      |
+-----+----------+
| 444 | 111      |
+-----+----------+
| 555 | null     |
+-----+----------+
| 666 | 222      |
+-----+----------+

GetChildren('111') will produce:

+-----+
| Id  |
+-----+
| 111 |
+-----+
| 444 |
+-----+

GetChildren('222') will produce:

+-----+
| Id  |
+-----+
| 222 |
+-----+
| 666 |
+-----+

I want to get table that is concatenation of 2 above:

+-----+
| Id  |
+-----+
| 111 |
+-----+
| 444 |
+-----+
| 222 |
+-----+
| 666 |
+-----+

If deleted would contain 4 rows I want to concatenate all 4 outputs of GetChildren for each row

The parent child is one level only or could be recursive ? child of child ..an so on.. ? — ScaisEdge
– ScaisEdge, Commented Jun 1, 2019 at 7:14

Brenton · Accepted Answer · 2019-06-01 07:56:24Z

3

You can use CROSS APPLY to return and union multiple resultsets from a table valued function based on rows from another table. In your example that would look like:

SELECT c.*
FROM deleted d
CROSS APPLY GetChildren(d.Id) c

answered Jun 1, 2019 at 7:56

Brenton

4542 silver badges12 bronze badges

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

2 Comments

IC_ Over a year ago

Can I do this for stored procedures the same way?

Brenton Over a year ago

No, this only works for user defined functions. To do something similar for a stored procedure you would need to do something like create a temp table then use a cursor to loop over the rows and insert the resultset of each procedure call into the temp table.

Collectives™ on Stack Overflow

Create a concatenated table from another table using function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related