Do for each - how to write it more graceful?

[Evgeny Gopengauz]

"Do something for each row of the query"
The only way I know is:

---
declare @C cursor
set @C= cursor for
select F from T where ...
declare @F int
open @C
while 0=0 begin
fetch next from @C into @F
if not(@@FETCH_STA TUS = 0) break
exec myStoredProc @F
end
close @C
deallocate @C
---

How to write it simpler, maybe with implicit cursors?
For example, in the Borland Interbase it would be like:

---
declare variable F integer;
for select F from T into :F
do execute procedure myStoredProc :F;
---


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Portas]

Re: Do for each - how to write it more graceful?

What does myStoredProc actually do? If the procedure just does some data
manipulation then maybe you can rewrite that code based on your cursor
query. For example:

SELECT ....
FROM Something
WHERE f
IN
(select F from T where ...)

As another alternative to a cursor you can try something like this, which
may be reasonably acceptable if the column F is unique and indexed.

DECLARE @f INTEGER

WHILE EXISTS
(SELECT *
FROM T
WHERE f>@f
OR @f IS NULL)
BEGIN
SET @f =
(SELECT MIN(f)
FROM T
WHERE f>@f
OR @f IS NULL)
EXEC myStoredProc @F
END

--
David Portas
SQL Server MVP
--

[Evgeny Gopengauz]

Re: Do for each - how to write it more graceful?

Thank you for these two ideas!
But... first case is not quite fit for me. My stored procedure is too
complicated, it makes some queries to remote server, calls some extended
procedures and so on.
Second case seems better, but (IMHO) it's a trick. And this trick (IMHO)
is too slow relative to case with cursor and fetch. And it looks like
T-SQL syntax limitation - no any common and short way to do something
for each row, like cycle by select :(

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Portas]

Re: Do for each - how to write it more graceful?

Other options:

1) Build a Dynamic SQL statement in a loop and then execute it.

2) Put the loop in the middle tier or client code.

3) For non-production use you could try the undocumented xp_execresultse t.
Example:

EXEC master..xp_exec resultset
'SELECT ''EXEC myStoredProc ''+CAST(f AS VARCHAR) FROM T','DBNAME'

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Re: Do for each - how to write it more graceful?

Evgeny Gopengauz (evgop@ucs.ru) writes:[color=blue]
> "Do something for each row of the query"
> The only way I know is:
>
> ---
> declare @C cursor
> set @C= cursor for
> select F from T where ...
> declare @F int
> open @C
> while 0=0 begin
> fetch next from @C into @F
> if not(@@FETCH_STA TUS = 0) break
> exec myStoredProc @F
> end
> close @C
> deallocate @C
> ---
>
> How to write it simpler, maybe with implicit cursors?[/color]

There are a couple of options, you can use SELECT MIN or SELECT TOP 1.
I would however recommend to stick with the cursors, they are in my
opinion the best way to iterate when you need to iterate. Solutions
with MIN or TOP 1 can have bad performance if there is no good index.

One tip is to make the cursor INSENSITIVE, since keyset-driven cursors
(the default) can sometimes have absymal performance when nailing
down which rows to operate on. Also insensitive saves you from
surprises if you update rows selected by the cursor.

Finally, I never use cursor variables, but always static names.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp