views:

209

answers:

7

Hi all,

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  A: 

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
Rubens Farias
+3  A: 

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
treaschf
To the OP, note that cursors are evil and you should avoid them at all costs, but I still give this a +1 because using them can be justified in certain cases like this. Hopefully this is a one-time thing and you aren't going to put this cursor inside another proc :)
Scott Anderson
(-1) Cursors are HORRIBLE especially since the new versions of SQL Server. That and Set based syntax is far more elegant and succinct -- not to mention orders upon orders of magnitudes faster.
Hassan Syed
Cursors are not evil - that's simplistic. If possible, re-write iterative solutions (cursors or while loops) using a set based solution. Without additional information you need to iterate in this case and a cursor is fine.
Precipitous
+4  A: 

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 Set @Key = 0
While Exists (Select * From @Keys
              Where key > @Key)
 Begin
     Select @Key = Min(Key) From @Keys
                   Where key > @KeyId
     EXEC UpdateComputedFullText @Key
     -- Delete @Keys Where Key = @Key No Longer necessary 
 End    
Charles Bretana
This is an interesting approach. :)
treaschf
ok, cursors are slow, but is this faster?
Rubens Farias
I like this. Cursors are evil. :)
Scott Anderson
A forward only read only cursor might perform better than this. But of course, performance must be measured, in order to decide.
treaschf
@treasch, you might be right about forward readonly cursor... I'd test that to see ...
Charles Bretana
@Rubens, Yes much faster, except possibly for foreward readonly, but (imho) it's easier and more straightforward as well (compare the code)...
Charles Bretana
This wins my first ever mod down - it's got 4 points but is mis- information. This will be slower than a cursor and gains nothing. I've seen the pattern used -- it is a defect to be fixed. Delete is your slowest SQL operation, and the cost is unnecessary in iteration. The other example of a while is preferable (from Jose). A fast forward cursor is fine. Set based would be best - but need more information to provide that.
Precipitous
This is not a bad approach. There are many cases where this approach out-does other approaches -- We deal queries that keeps terabytes of data in query scope -- and when this is the case you have to use a temp table of some form. As precipitous mentions delete is slow and can be avoided -- such as with a `CROSS-APPLY` and a table value function see my answer :D.
Hassan Syed
#Precipitous, You are correct that a set-based approach would be faster, but it's not that not enough info is available to suggest one, rather, you cannot apply a set-based approach against an existing SP that takes a single key value. The SP would have to be rewritten. The OP, it seems, is explicitly asking for a key-based approach for that reason. But Delete is not slow when used with a filter predicate driven against a very narrow unique index, as this is. And it can easily be avoided, just by making loop as shown in edited answer.
Charles Bretana
+2  A: 

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.

Bob Palmer
+1  A: 

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 
Jose Chama
This is dogma without reason - letter of the law instead of spirit of the law. The primary reason to avoid cursors is because there is almost always a more efficient set based solution. This avoids cursors but does not provide a set based solution. Of course, we don't know that there exists a set based solution to this problem.
Precipitous
(+1) I've been told by my colleagues who do terabyte scale data processing in SQL that a `WHILE` for iteration performs quite well -- especially when no choice remains -- these days however TVF + CROSS APPLY = king (see my answer).
Hassan Syed
A: 

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.

bretlowery
+2  A: 

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 querry 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 querry 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.

Hassan Syed
(+1) I am always open to new solutions, specially if they are set base.
Jose Chama