Calling table-valued-function for each result in query
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. H开发者_StackOverflow中文版ere 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)
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
精彩评论