This procedure performs the same changes to the underlying table as the procedure written without a cursor, but it uses cursors instead of set-oriented programming. As each row is fetched, examined, and updated, a lock is held on the appropriate data page. Also, as the comments indicate, each update commits as it is made, since there is no explicit transaction.
/* Same as previous example, this time using a ** cursor. Each update commits as it is made. */ create procedure increase_price_cursor as declare @price money /* declare a cursor for the select from titles */ declare curs cursor for select price from titles for update of price /* open the cursor */ open curs /* fetch the first row */ fetch curs into @price /* now loop, processing all the rows ** @@sqlstatus = 0 means successful fetch ** @@sqlstatus = 1 means error on previous fetch ** @@sqlstatus = 2 means end of result set reached */ while (@@sqlstatus != 2) begin /* check for errors */ if (@@sqlstatus = 1) begin print "Error in increase_price" return end /* next adjust the price according to the ** criteria */ if @price > $60 select @price = @price * 1.05 else if @price > $30 and @price <= $60 select @price = @price * 1.10 else if @price <= $30 select @price = @price * 1.20 /* now, update the row */ update titles set price = @price where current of curs /* fetch the next row */ fetch curs into @price end /* close the cursor and return */ close curs return
Which procedure do you think will have better performance, one that performs three table scans or one that performs a single scan via a cursor?