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 Top 1 @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 @HoldTable SELECT Id FROM Table1
WHILE EXISTS(SELECT * FROM @HoldTable)
BEGIN
 SELECT @Id = id from @HoldTable
 EXEC dbo.Sproc @Id
 DELETE FROM @HoldTable where Id = @Id
END

Answer 2

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.

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

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

Answer 5

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 6

Take a look at this answer using dynamic SQL. Here is what looks like based on your query.

DECLARE @TSQL NVARCHAR(MAX) = ''
SELECT @TSQL = @TSQL + N'EXEC dbo.Sproc ' + id + '; 
' -- To insert a line break
FROM Table1
EXEC sp_executesql @TSQL

This equals to the following if you PRINT @TSQL. Note that how the line break is inserted.

EXEC dbo.Sproc id1; 
EXEC dbo.Sproc id2; 
EXEC dbo.Sproc id3;
...

If you don't need a stored procedure, it can be easier. I once had a need to update a table based on each row (Code is the column name, and [Value] the value) from a view. Because the view is dynamic and returns different rows each time, and hence different column-value pairs. I re-wrote the query without using any stored procedure.

DECLARE @TSQL NVARCHAR(MAX), @Id VARCHAR(10) = 50

SELECT @TSQL = COALESCE(@TSQL + '''
,','') + Code + ' = ''' + [Value] 
FROM view
WHERE Id = @Id

SET @TSQL = N'UPDATE tableName
SET ' + @TSQL + ''' 
WHERE Id = ' + @Id
PRINT @TSQL
--EXEC SP_EXECUTESQL @TSQL

PRINT @TSQL:

UPDATE tableName
SET Discussed = 'Yes'
,Other = 'Blue'
,IntakeRating = 'H: < $25k'
,IntakeValueEstimate = '25'
,OpportunityDecision = 'Intake'
,Fee = '33 1/3'
,Fee2 = '40'
,IsManager = 'Yes' 
WHERE Id = 50

Answer 7

This loop works, but it was slow for my SP. I think the correct answer is by HLGEM, your best bet is to write a better bulk query.

DECLARE @id INT
SET @id = 0

DECLARE @max INT
SELECT TOP 1 @max = TableID
FROM dbo.Table
ORDER BY TableID desc

-- loop until BREAK
-- this is how you can perform a DO-WHILE loop in TSQL
WHILE (1 = 1) 
BEGIN
    SELECT      
        TOP 1 @id = TableID
        FROM dbo.Table
        WHERE TableID > @id 

    IF @id IS NOT NULL
    BEGIN        
        -- call you SP here
        PRINT @id        
    END

    -- break out of the loop once the max id has been reached
    IF @id = @max BREAK 
END