Calling table-valued-function for each result in query

Question

Say I had a query like this:

SELECT X FROM Table WHERE Y = 'Z'

How could I execute a Stored Procedure using each X from the above query as the parameter?

UPDATE

I have changed the SP to be a Table-valued function instead. So for each call to the function it will return a table. What I need to do is store all these results in perhaps a temp table and have my SP return this table.

SOLUTION

Finally managed to get this to work with some help from @cyberkiwi. Here is my final solution:

DECLARE @Fields TABLE (
    Field int)

INSERT INTO @Fields (X) SELECT * FROM tvf_GetFields(@SomeIdentifier)
SELECT * FROM @Fields
CROSS APPLY dbo.tvf_DoSomethingWithEachField([@Fields].Field)

Answer 1

You can generate a batch statement out of it and then EXEC it

DECLARE @sql nvarchar(max)

SELECT @sql = coalesce(@sql + ';', '')
              + 'exec sprocname ' + QuoteName(AField, '''')
FROM Table
WHERE AField2 = 'SomeIdentifier'
  AND AField is not null

EXEC (@sql)

Before the edit (to TVF), you could have changed the SP to continue to populate a temp table.

Post-edit to TVF, you can use a cross apply:

SELECT F.*
FROM Tbl CROSS APPLY dbo.TVFName(Tbl.AField) F
WHERE Tbl.AField2 = 'SomeIdentifier'

Which returns all the "table results" from each invocation of Tbl.AField into a single result set