Sunday, April 13, 2014

Simple Example of Cursor in SQL Server

Here I will explain how to create cursor in Sql Server 2008 using sql query.

What is cursor in SQL Server?

A cursor is a set of rows with a pointer that identifies a current row(for example if you have set of rows together and wants to perform some action like insert update or delete repeatedly with different values that is store in rows set format and pointer is pointing to row one by one until reached at last row).
 
In other word, Cursor is a database object used by many applications to easly manipulate data in a set on a row-by-row basis, its like recordset in the ASP.

Example:


Declare @ID as int
Declare @Name as varchar(50)
Declare @Email as varchar(20) 
Declare @Location as varchar(20) 

  --declare the UserData cursor   
  Declare UserData CURSOR FOR

  Select Name ,Email, Location from  tblUser

     --now we need to open cursor and tell it to go
     OPEN UserData

     --now fetch first row from table tblUser 
      FETCH NEXT FROM UserData INTO @Name ,@MOBILE ,@Location

      WHILE @@FETCH_STATUS = 0
          BEGIN 
                  --action 
                   Insert Records @Name ,@MOBILE , @Location

      --fetch row values until end of the row 
      FETCH NEXT FROM UserData INTO @Name ,@MOBILE ,@Location
        END

   --close cursor

  CLOSE UserData

--Deallocate cursor

DEALLOCATE UserData




No comments:

Post a Comment