Executing SQL function for each row in the table

Question

I have implemented a SQL function and I want to execute it for every row returning from a table. For simplicity, let’s assume that a function accepts @StudentId as parameter and returns a table/multiple rows with Subject and Score as columns.

I want to execute this function for every student returning from a table say Students and aggregate the data in a temp table with following format:

StudentId |Subject| Score

One way I can think of is writing a cursor over Students table, executing the function for each student and collecting the information in a temp table. Is this the only option I have or is there any better way to handle this?

I am using SQL server 2008 R2.

Amey

Answer 1

Well, without proper specs, I can only offer that you start with something like this (please stop and provide more details before writing any cursors, loops or temp tables):

SELECT s.StudentId, f.someColumn FROM dbo.Students AS s
  CROSS APPLY dbo.FunctionName(s.StudentId) AS f;

Also, if you provide more details about the function itself, we may have input on better ways to write it (or to not use a function at all).