Cursor In Sql Server

Cursor In Sql Server

Cursor is a database Object used to manipulate data on a row-by-row basis ..

Following are step to create Cursor .

Declaring a Cursor
Before using cursor, you first must declare the cursor, i.e. define its scrolling behavior and the query used to build the result set on which the cursor operates. To declare cursor, you can use a syntax based on the SQL-Cursur standard and a syntax using a set of Transact-SQL extensions.
This is SQL-Cursur Syntax:

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR

FOR select_statement

[FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]

Following is Table-Value Function In which I have Used Cursor ::

FUNCTION [dbo].[fntbGetRevenueOfEmployees]()
RETURNS @EmployeeRevenue TABLE ( OrderDate Date , EmployeeID int ,Employee1 decimal(16,2) , Employee2 decimal(16,2), Employee3 decimal(16,2) ,Employee4 decimal(16,2)  , 
Employee5 decimal(16,2)   )
AS
BEGIN        
DECLARE  Name_Cursor CURSOR FOR 
Select  convert( varchar , CustomerOrders.OrderDate ,101)  ,         Employees.EmployeeID , sum(dbo.fnItemPriceTotal(customerorderDetails.Qty        ,customerorderDetails.Price,ISNULL(customerorderDetails.Double1,0))) as TotalForRow From  CustomerOrders
left join customerorderDetails on CustomerOrders.OrderID = customerorderDetails.OrderID
left join Accounts on Accounts.AccountID =  CustomerOrders.CustomerID
left join Employees ON Accounts.SalesPersonID = Employees.EmployeeID
group by convert( varchar , CustomerOrders.OrderDate ,101)  , Employees.EmployeeID
order by convert( varchar , CustomerOrders.OrderDate ,101)

DECLARE @C_OrderDate  as Datetime
DECLARE @C_OrderTotal as  decimal(16,2)
DECLARE @C_EmployeeID as Int 
Declare @intCount as int
declare @TempEmpId as int
set @intCount = 0

OPEN Name_Cursor;
FETCH NEXT FROM Name_Cursor INTO @C_OrderDate ,@C_EmployeeID , @C_OrderTotal ;
WHILE @@FETCH_STATUS = 0
BEGIN
 
   insert into @EmployeeRevenue values( convert(varchar , @C_OrderDate ,101) , @C_EmployeeID , @C_OrderTotal , 0 ,0,0,0 ) ;

   FETCH NEXT FROM Name_Cursor INTO @C_OrderDate ,@C_EmployeeID , @C_OrderTotal ;

END
close Name_Cursor  
deallocate Name_Cursor  
return  
end

Related Other posts

Sorting, Paging, Add, Update, Delete in GridView Check / Uncheck all checkbox in treeview using jquery Image In Datalist Merge docs files in c#

Bind Treeview in asp.net c# using dataset CheckboxList example Datetime format in gridview Send mail in asp.net c#

Highest second record in sql Nested gridview Checkboxs in gridview Validation using jquery in asp.net page