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
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
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.
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