SQL - Call Stored Procedure for each record

问题:

I am looking for a way to call a stored procedure for each record of a select statement.

SELECT @SomeIds = (
    SELECT spro.Id 
    FROM SomeTable as spro
    INNER JOIN [Address] addr ON addr.Id = spro.Id 
    INNER JOIN City cty ON cty.CityId = addr.CityId
    WHERE cty.CityId = @CityId
)


WHILE @SomeIds  IS NOT NULL
BEGIN
    EXEC UpdateComputedFullText @SomeIds
END

Such a thing above is not working of course, but is there a way to do something like that?

回答1:

You need to use a cursor for that.

DECLARE @oneid int -- or the appropriate type

DECLARE the_cursor CURSOR FAST_FORWARD
FOR SELECT spro.Id  
    FROM SomeTable as spro 
        INNER JOIN [Address] addr ON addr.Id = spro.Id  
        INNER JOIN City cty ON cty.CityId = addr.CityId 
    WHERE cty.CityId = @CityId

OPEN the_cursor
FETCH NEXT FROM the_cursor INTO @oneid

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC UpdateComputedFullText @oneid

    FETCH NEXT FROM the_cursor INTO @oneid
END

CLOSE the_cursor
DEALLOCATE the_cursor

回答2:

Surprised no one gave you an up-to-date answer. Cursors are bad. What you want is to move the logic of the SP into a table-valued-function(TVF) and then use CROSS APPLY

Here is a query I wrote yesterday (don't dwell on the details, just look at the CROSS APPLY). The CROSS APPLY creates a union of tables. Each element of this union is generated from the TVF which is parameterised on the row entries of the select statement.

SELECT supt.hostname,supt.scriptname, COUNT(*)
FROM Event_Pagehit eph
    INNER JOIN Symboltable_urlpair supf
    ON eph.fromPagePair=supf.id
    INNER JOIN Symboltable_urlpair supt
    ON supt.id=eph.toPagePair
CROSS APPLY dbo.TDFCompanyFormationsUrlClassification(supf.hostname,supf.scriptname) as x
CROSS APPLY dbo.TDFCompanyFormationsUrlClassification(supt.hostname,supt.scriptname) as y
WHERE x.isCompanyFormations=1
AND y.isCompanyFormations=0
GROUP BY supt.hostname,supt.scriptname
ORDER BY COUNT(*) desc

I can use x and y as if they were tables pulled in from the FROM or JOIN clauses. If I had to write this query without a TVF it would span a couple of hundred lines.

Note:

If you can't rewrite the SP: you should be able to insert the result of a stored procedure into the result table from a table valued function. I have never done this, and sometimes the different SQL server construct have caveats -- So unless someone says otherwise I assume this is the case.

回答3:

Put the Ids into a Temporary table variable, and then iterate throught each row: (You do not need to use a cursor which will be considerably slower)

   Declare @Keys Table (key integer Primary Key Not Null)
   Insert @Keys(key)
   SELECT spro.Id  
   FROM SomeTable as spro 
       JOIN [Address] addr ON addr.Id = spro.Id  
       JOIN City cty ON cty.CityId = addr.CityId 
   WHERE cty.CityId = @CityId
   -- -------------------------------------------
   Declare @Key Integer
   While Exists (Select * From @Keys)
     Begin
         Select @Key = Max(Key) From @Keys
         EXEC UpdateComputedFullText @Key
         Delete @Keys Where Key = @Key
     End 

EDIT Delete is not slow when used with a filter predicate driven against a very narrow unique index, as this is. But it can easily be avoided, just by making loop as follows:

Declare @Key Integer = 0
While Exists (Select * From @Keys
              Where key > @Key)
 Begin
     Select @Key = Min(Key) From @Keys
                   Where key > @Key
     EXEC UpdateComputedFullText @Key
     -- Delete @Keys Where Key = @Key No Longer necessary 
 End    

回答4:

Try this one without cursor

DECLARE @id int 

SELECT top 1 @id = spro.Id   
    FROM SomeTable as spro  
        INNER JOIN [Address] addr ON addr.Id = spro.Id   
        INNER JOIN City cty ON cty.CityId = addr.CityId  
    WHERE cty.CityId = @CityId
    ORDER BY spro.id

WHILE @@ROWCOUNT > 0 
BEGIN 
    EXEC UpdateComputedFullText @id 

    SELECT top 1 @id = spro.Id   
    FROM SomeTable as spro  
        INNER JOIN [Address] addr ON addr.Id = spro.Id   
        INNER JOIN City cty ON cty.CityId = addr.CityId  
    WHERE cty.CityId = @CityId 
    and spro.id > @id
    ORDER BY spro.id
END 

回答5:

Both of the answers above RE cursors are correct. However, based on the complexity of the code running inside of the cursor, you may be better served in dropping this into your language of choice and performing your calculations in code before dropping the results to a database.

I've found myself going back and reviewing a lot of cursor operations, and in many cases, transitioning these to code for performance reasons.

回答6:

You'll need to use a cursor: SQL Server Cursor Examples

DECLARE @id int
DECLARE cursor_sample CURSOR FOR  
SELECT spro.Id 
FROM SomeTable as spro
    INNER JOIN [Address] addr ON addr.Id = spro.Id 
    INNER JOIN City cty ON cty.CityId = addr.CityId
WHERE cty.CityId = @CityId

OPEN cursor_sample
FETCH NEXT FROM cursor_sample INTO @id 
WHILE @@FETCH_STATUS = 0   
BEGIN  
    EXEC UpdateComputedFullText @id
    FETCH NEXT FROM cursor_sample INTO @id
END   

CLOSE cursor_sample
DEALLOCATE cursor_sample

回答7:

Do you really need to do row-by-row processing when set processing is available?

You could put the results of the SELECT into a temp table, then call a proc to perform bulk SQL against the contents of the temp table. The temp table will be available to the called proc based on T-SQL scoping rules.

回答8:

The standard cursor solution is evil upon evil. Two identical FETCH NEXT statements are just a maintenance nightmare.

better is

...declare cursor etc.
While 1=1
 Fetch ...
 if @@FETCH_STATUS <> 0  BREAK
...
End -- While 
..Close cursor etc.

An evil sometimes justified. Just try to devise a set based approach to sending notification emails using sp_send_dbmail or other stored procedure.