sqlsql-servert-sqlstored-procedures

Is it possible to execute a stored procedure over a set without using a cursor?

I would like to execute a stored procedure over each row in a set without using a cursor with something like this:

SELECT EXEC dbo.Sproc @Param1 = Table1.id
FROM Table1

I am using T-SQL in SQL Server 2005. I think this might be possible using a function, but I'd like to use a stored procedure if possible (company standards)

Solution

  • 9 out of 10 times you can do what you want without a cursor or a while loop. However, if you must use one, I have found that while loops tend to be faster.

    Also if you do not want to delete or update the table, you can use something like this:

    DECLARE @id [type]
SELECT @id = MIN([id]) FROM [table]
WHILE @id IS NOT NULL
BEGIN
    EXEC [sproc] @id
    SELECT @id = MIN([id]) FROM [table] WHERE [id] > @id
END