Introduction:

In this article, we are going to explain what is Cursor in SQL or how to create Cursor in SQL or how to use Cursor in SQL server with an example.

Description:

A Cursor is a SQL Object, or we can say like a Virtual table that retrieves data from the table one row at a time. We use cursors when we need to update or delete records in a database row by row. You can evaluate data row-by-row. Think of cursors as a stored data set that then lets you go through each row, manipulate or view a field, and then perform some kind of logic against the record based on a field's value. In most cases, you don't need a cursor in your code, but it's good to understand cursor syntax and how it functions differently from other SQL code.

Let’s see simply how to create a cursor

Declare @ID as bigint
Declare @Name as varchar(120)
Declare @Address as varchar(200)
Declare MY_CURSOR CURSOR FOR
Select ID ,Name,Address1 from CUSTOMER (Nolock)
OPEN MY_CURSOR
  PRINT ' Customers '
  PRINT '=============================='
    FETCH NEXT FROM MY_CURSOR INTO @ID ,@Name,@Address
        WHILE @@FETCH_STATUS = 0
        BEGIN
  PRINT 'ID :'+CAST(@ID AS VARCHAR(15))
  PRINT 'ID :'+CAST(@ID AS VARCHAR(15))
  PRINT 'Name :'+ @NAME
  PRINT 'Address :'+@Address
  PRINT'______________________'
        FETCH NEXT FROM MY_CURSOR INTO @ID ,@Name,@Address
        END
    CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

In above example, we have created a cursor that fetch customer record row by row and print formatted design which shown below: 

OutPut:

create a simple cursor in sql server

I hope it will help you after reading it.

You might also like to read  Multi-statement Table-Valued User-Defined Function in SQL Servertable valued User-Defined Function in SQL Server, scalar User-Defined Function in SQL Server


  Modified On Sep-07-2019 12:18:21 AM

Leave Comment