Run SQL function for each returned record

Question

I have a sql function that calculate some numbers and returns 1 to 4 records for each run, the function gets clientID (int).

I have a query that selects ALL clients. I want that on each client the function will run and will return its values. something like that:

ClientID | FunctionReturnValue
   1               1.1
   1               2.1
   1               1.9
   1               1.3
   2               1.9
   3               1.3

Any ideas?

When you say "function". You mean table valued function? If so why is this tagged "stored-procedures"? — Martin Smith
– Martin Smith, Commented Sep 9, 2013 at 9:49

DaveShaw · Accepted Answer · 2013-09-09 10:17:56Z

3

You just use the function in your select:

For example if you have a scalar function.

select
    ClientId,
    dbo.YourFunction(ClientId) as FunctionReturnValue
from YourTable

If you have a Table Value Function try this:

select
    ClientId,
    FunctionReturnValue
from YourTable
cross apply dbo.YourFunction(YourTable.ClientId)

edited Sep 9, 2013 at 10:17

answered Sep 9, 2013 at 9:41

DaveShaw

52.9k18 gold badges118 silver badges142 bronze badges

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

1 Comment

Martin Smith Over a year ago

You are assuming a scalar UDF which seems unlikely from the description. Scalar UDFs return a single value not 1 to 4 records

Collectives™ on Stack Overflow

Run SQL function for each returned record

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related