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

Question

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)

Answer 1

Yes. If you have a column you can use within the table to mark the ones processed, you can use WHILE EXISTS:

DECLARE @Id int
WHILE EXISTS(SELECT * FROM Table1 WHERE Processed='N'
BEGIN
 SELECT @Id = id from Table1 WHERE Procesed='N'
 EXEC dbo.Sproc @Id
 UPDATE Table1 SET Processed = 'Y' WHERE Id = @Id
END

Alternately, dump the ids into a temp table or table variable and delete when finished:

DECLARE @HoldTable table (Id int PRIMARY KEY)
DECLARE @Id int
INSERT INTO @Id SELECT Id FROM Table1
WHILE EXISTS(SELECT * FROM @HoldTable WHERE Processed='N'
BEGIN
 SELECT @Id = id from @HoldTable WHERE Procesed='N'
 EXEC dbo.Sproc @Id
 DELETE FROM @HoldTable where Id = @Id
END

Answer 2

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:

WHILE @id IS NOT NULL
BEGIN
    SELECT top 1 @id = MIN(id) FROM Table WHERE id > @id order by id
    IF @id IS NOT NULL
    BEGIN
        exec dbo.Sproc @Param1 = @id
    END
END

Answer 3

Frankly if I needed to execute a stored proc for a set of data instead if the one record it was written for , I would write the set-based code instead of using the stored proc. This is the only efficient way to do this if you are going to be running against a large data set. You could go from hours to do the insert that the stored proc does to seconds or even milliseconds. Do not use record based processing ever when you need to processs a set of data especially not for a silly reason like reuse of code. Performance trumps reuse of code.

Answer 4

If possible I'd write a second version of the stored proc that reads from a temp table.

If that's not possible than you're probably out of luck.

Answer 5

If you're only using SQL Server 2005 or newer, don't care about backwards compatibility and can convert your code to be in a User-Defined Function (rather than a stored proc) then you can use the new "CROSS APPLY" operator, which does use a syntax very similar to what you want. I found here a short intro (of course, you can also read the BOLs and MSDN)

Supposing your SP returns a single value named *out_int*, your example could be rewritten as:

SELECT T.id, UDF.out_int
FROM 
    Table1 T
CROSS APPLY
    dbo.fn_My_UDF(T.id) AS UDF

This will retrieve each "id" from Table1 and use it to call fn_My_UDF, the result of which will appear in the final result-set besides the original parameter.

A variat of "CROSS APPLY" is "OUTER APPLY". They are equivalents of "INNER JOIN" and "LEFT JOIN", but work on joining a table and a UDF (and calling the second at the same time).

If you must (by explicit order of the pointy-haired boss) use SPs insead, well - bad luck! You'll have to keep with cursors, or try cheating a bit: change the code into UDFs, and create wrapper SPs :D.