Is it possible to execute a stored procedure over

问题:

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)

回答1:

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

回答2:

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

回答3:

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.

回答4:

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.

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

回答6:

Take a look at this answer using dynamic SQL. I am going to rewrite the query to suit your need.

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 how the line break is inserted.

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

回答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