How to make or use Cursor in SQL Server

What is Cursor- Cursor is a control structure that enables traversal over the records in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records. The database cursor characteristic of traversal makes cursors akin to the programming language concept of iterator.

Cursors are used by database programmers to process individual rows returned by database system queries. Cursors enable manipulation of whole result sets at once—a capability that most procedural programming languages lack. In this scenario, a cursor enables the rows in a result set to be processed sequentially.

In SQL procedures, a cursor makes it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.

A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.
Declare variables that will need to be use in cussor like:-
          declare @vehicleID int
           declare @LocationId int
           declare @readedat int   
           declare @CreatedAt datetime
      
begin
           DECLARE CursorName CURSOR FOR
       
            select LocationId, DATEDIFF(mi, readedat,dateadd(mi,300,getdate()))as readedat,speed*@Cspeed from TblArea where  speed*@Cspeed*0.6213>20
            OPEN CursorName   
                  FETCH NEXT FROM CursorName INTO @LocationId, @readedat,@speed
                                     
                      WHILE @@FETCH_STATUS = 0                     
                          BEGIN   
                                   select @vehicleID =vehicleID from vehicle where LocationId=@LocationId
                                   if(@speed*@Cspeed*0.6213>40)
                                       begin
                                       --Put you logic here
                                      end              
                                      
                                  set @LocationId=0
                                  FETCH NEXT FROM CursorName INTO @LocationId, @readedat,@speed  
                           END   
              CLOSE CursorName             
              DEALLOCATE CursorName             
        end      
      

 

  Copy Right © InfoA2Z.co