Code Project

Link Unit

Friday, May 12, 2006

Is Using Cursor Good?

We cannot avoid the usage of cursor in certain cases , but most of the
time appropriate where clauses can be used and hence cursor can be
avoided.
Let us take a simple example where a person's salary is to be increased
by 10% if his salary is <1000

and decrease by 10% if salary >=1000

If we use these two statements

Update emp set salary = salary *1.10 where salary <1000

Update emp set salary = salary *0.90 where salary >=1000

It will correctly update the salary of most but the one which fall in
the range ( 900-1100) would be afftected
So what normal procedural approach suugests. declare a cursor open cursor fetch row by row till @@fetch_status = 0 if salary <1000 then increase otherwise decrease for each row. close cursor
But with the introduction of 'case when' expression we can use
something like this
update emp set salary = salary * (case when salary<1000 then 1.10 else
0.90 end)
The only place I found it useful is where we need to do some DBA
activity. e.g truncate all tables (although it can be done with single
undocumented procedure 'sp_MSforeachtable' in SQL Server )
Exec sp_MSforeachtable 'truncate table ?' -- this will truncate all the
tables.
Cursor is like an evil beast we must learn to live without. Cursor is
not meant for DML activities. It should be used for DBA activities
only.

Conclusion: One must use the power given by the features not abuse them. Overusing
cursors for all activities means abusing the feature given.
Post a Comment