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
- Static
- Dynamic
- Forward-Only
- 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