SQL Looping through results

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

1 sql
13.10.2009 16:04:47
What DBMS are you using? What is your front-end application written in? MySQL, PHP or others?
Lukasz Lysik 13.10.2009 16:07:35
what language are you trying to do this in?
kscott 13.10.2009 16:07:43
We need more content in order to help - what table(s) & their columns, and what specifically are you wanting to calculate?
OMG Ponies 13.10.2009 16:08:42
Loops/cursors are generally slow and it is almost always possible to replace them with SQL that doesn't have loops or cursors, especially in SQL 2008
MartW 13.10.2009 16:12:49
I don't see how... I want to run a function on every user in my database, therefore I need to loop through them all surely?
Bobbin 13.10.2009 16:14:21
3 ОТВЕТА
РЕШЕНИЕ

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
2
13.10.2009 16:40:32
I thought this only works with UDFs, not stored procedures. It doesn't seem to work in SQL2008 R2
MartW 13.10.2009 16:33:29
That's pretty much like looping. I avoid using scalar udfs becasue they act row-by-row.
HLGEM 13.10.2009 19:14:32

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.

1
13.10.2009 16:08:37

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
1
13.10.2009 16:26:47