I am coding application according design and many time there is need to rewrite commands like these:

foreach ( select @x = col ... from ... )
update, insert, exec...etc

endforeach

I d have some philosophical questions...

1) what do you use to rewrite this ? E.g. you automative, without thinking, write cursor according your template - it is fastest, easy and effective way how to re-write this

2) you use other way cos you concern about amount of written code and his simply fast re-reading in the future by other programmers ? E.g. cursor are heavy, lot of code etc. so we use simply temp table with indexed column and counter...

3) what concerns of effectivity and resource cost? You mostly use cursors/ temporary tables/db work tables cos ...... .

Conlusion first we use .... and if we see during testing problems we rewrite and use this ...

Thanks for your hints and points of view...

The basic thumb rule for databases is the more you batch, the better your performance is. Which translates to

a) Temp tables are more batch based.
b) Cursors are more row based, so less throughput. Wherever you can avoid cursors, it would be prefereble.

is temp tables based solution better solution even if I am going through temp table row-by-row with counter variable holding index column...?

Cursors are always going to be slow and difficult to read but if the application works and it's fast enough then why bother changing it? At best it will be wasted effort and at worst you'll just be introducing new errors.

If the application too slow then only change those cursors that affect the performance most.

If money is no object then sure - get rid of all the cursors

Thanks for tips and hints

Problem is that I am almost ever in transaction (where DDL is prohibited, so create table is disabled in tran). Moreover working on just part of large application in deep level on one called stored procedure.

So, if I implement new code with foreach... it seems for me most simple/fast to change just one procedure and implement cursor... otherwise I d have to look for all callings of my procedure and spoting where transaction starts in these procedures and implement create temp table before begin tran in every calling...

And instead one stored proc simple test all these changed procedures I d have to test subseqently...