SQL Server Cursor problems

Question

I have heard that there are many problems to use cursor in SQL Server like performance and it is considered a bad practice to use it

So I want to know when should and shouldn't use cursors in SQL Server and what are cursors alternatives

Answer 1

What is cursor

• cursor is an object that enables traversal over the rows of a result set.
• It allows you to process individual row returned by a query.
• Cursor is used for update the table on a row-by-row basis

Note: the Cursor is very bad for performance, and should be avoided always. So you can use joins instead of the cursor

There are different types of cursor

1. Static
2. Dynamic
3. Forward-Only
4. Keyset

Here is the example for how to use a cursor and join

I have two tables about product and salesproduct and I want to update the price of Hp laptop and Dell laptop.

1- First using Cursor

Declare @ProductId int
Declare ProductCursor CURSOR 
FOR 
Select ProductId from ProductSales

Open ProductCursor

Fetch Next from ProductCursor into @ProductId


While(@@FETCH_STATUS = 0)
Begin
 Declare @ProductName nvarchar(50)
 Select @ProductName = Name from Products where Id = @ProductId
 
 if(@ProductName = 'Hp laptop')
 Begin
  Update ProductSales set Price = 5000 where ProductId = @ProductId
 End
 else if(@ProductName = 'Dell laptop')
 Begin
  Update ProductSales set Price = 7000 where ProductId = @ProductId
 End
 Fetch Next from ProductCursor into @ProductId 
End

CLOSE ProductCursor 

DEALLOCATE ProductCursor

2- Second using Join

Update ProductSales
set UnitPrice = 
 Case 
  When Name = 'Hp laptop' Then 5000 
  When Name = 'Dell laptop' Then 7000 

 End     
from ProductSales
join Products
on Products.Id = ProductSales.ProductId
Where Name = 'Hp laptop' or Name like 'Dell laptop'

For more details, See also SQL Server Cursors