The table-value function call for each query result

Question

Say I had a query like this:

SELECT X FROM Table WHERE Y = 'Z' 

How can I execute a stored procedure using each X from the above query as a parameter?

UPDATE

I changed the SP so that there was a table function instead. Therefore, for each function call, it returns a table. What I need to do is save all these results, possibly in the temp table, and my SP will return this table.

Decision

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

 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 create a batch statement from 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 editing (on TVF), you could change the SP to continue filling out the pace table.

After editing in TVF, you can use the cross:

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

Which returns all the "table results" from each call to Tbl.AField in a single result set