SQL Looping through results

Question

I do a select and it brings back a list of IDs. I then want to call another procedure for each ID that calculates a result, so I end up with a list of results.

How can I do this? I need some kind of loop but I am not very good at SQL.

Edit: Microsoft SQL 2008, and purely in SQL

Answer 1

You can have a stored procedure that calls the select to get the IDs, use a cursor on the result list and call the other procedure for each ID. All inside a stored procedure.

Answer 2

Write a user defined function that takes in the ID and returns the calculated result you can then get that result for each ID with a query like this:

SELECT id, DatabaseYouUsed.dbo.functionYouWrote(id)
FROM DatabaseYouUsed.dbo.TableWithIDs

Answer 3

If one row generates one result:

CREATE FUNCTION f(x int) RETURNS int AS BEGIN
    RETURN x * 2
END
GO
SELECT id, x, dbo.f(x) FROM my_table

If one row might generate more than one result:

CREATE FUNCTION f(x int) RETURNS @r TABLE(result int) AS BEGIN
    INSERT INTO @r VALUES(x)
    INSERT INTO @r VALUES(x * 2)
    RETURN
END
GO
SELECT t.id, t.x, r.result FROM my_table t CROSS APPLY dbo.f(t.x) r