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)

Is there any possibility of converting the SP into a function? Or, if the SP must remain, move the guts of it into a function then call the function from the SP and from this code? — Daniel Renshaw
– Daniel Renshaw, Commented Jan 25, 2011 at 9:55
@Daniel: I have converted the SP into a function, still can't quite get this to work. — James
– James, Commented Jan 25, 2011 at 10:28

RichardTheKiwi · Accepted Answer · 2011-01-25 12:13:27Z

14

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

edited Jan 25, 2011 at 12:13

answered Jan 25, 2011 at 11:49

RichardTheKiwi

108k28 gold badges206 silver badges268 bronze badges

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

Collectives™ on Stack Overflow

Calling table-valued-function for each result in query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related