Cursor in SQL(Procedures)

Share us on:

Here, Learn Cursor in SQL Server(Procedure) step by step with an example by Shubham Kumar.

Cursor plays a very important role in SQL Server, It's used to we can iterate(loop) and read Data one by one.

MSSQL Provide Cursor, It's used to we can read data one by one.

In Many situations, we need cursors, If SQL Query returns multiple records and you want to calculate and change data and add to another table row by row then you can use the cursor in SQL Server(Procedure).

What is Cursor in SQL Server(Procedure)

A cursor is a database object, it read data row by row from the database and the user can perform work related to this.

There are two types of the cursor in SQL Server 

       1. Implicit Cursors

           An implicit Cursor is a system-defined Cursor, It is used when DML(Data Manipulation Language) Operation is performed.

           It is the default Cursor Of the SQL Server. 

       2. Explicit Cursors

           An explicit Cursor is a user-defined cursor, If we want to be read or fetch data row by row then can use Cursor.

Hi, I am Shubham Kumar, I am going to explain how to create a cursor with an example in SQL Server. 

1. Declare Cursor in procedure

2. Open Cursor and get data with a select statement query from the database.

3. Fetch Data row by row from database

4. Check Condition, data exist or not.

5. Read Data From the cursor and perform logic row by row.

6. Close and Deallocate Cursor.      

7. example of the cursor.      

Step #1: Declare Cursor Syntax

You can declare Cursor with DECLARE keyword and after declaring keyword you need to provide cursor name and fetch data with the select statement.

There are given below, the syntax of declaring the cursor.

  DECLARE cursor_Data CURSOR
	FOR 
	SELECT Email, Password,userName FROM tblLogin;

Step #2: Open Cursor and get data with a select statement query from the database

Here, I am fetching data from the database with a select statement and reading data row by row by cursor object.

It is given below, the syntax for opening the cursor.

 OPEN cursor_Data;

Step #3: Fetch Data row by row from database.

  -- Read data From Cursor.
	 FETCH NEXT FROM cursor_Data INTO 
	  @Email, 
	  @Password,
	  @userName;

Step #4: Check Condition, data exist or not.

Here, we are checking Data fetch or not with @@FETCH_STATUS in the while loop, if Status is 0 its means data fetch, if Status is -1, it means The FETCH statement failed. If Status is -2, it means The row fetched does not exist.   

 WHILE @@FETCH_STATUS = 0

Step #5: Read Data From the cursor and perform logic row by row.

 WHILE @@FETCH_STATUS = 0
	BEGIN
	  INSERT INTO #UserDdata(Email,Password,userName)   VALUES(@Email,@Password,@userName)
	-- After Read data From Cursor then FETCH NEXT RECORD from Cursor.
	  FETCH NEXT FROM cursor_Data INTO 
		@Email, 
		@Password,
		@userName;
	END;

Step #6: Close and Deallocate Cursor.

After getting all records you can close and deallocate the cursor. 

--After Read all Record then It CLOSE THE CURSOR.
		CLOSE cursor_Data;
        DEALLOCATE cursor_Data;

Step #7: example of the cursor.

Here, we are taking temptable, where we can store value from cursor row by row and show its value after getting all data. 

CREATE PROC CursorExample
   AS
   BEGIN
      --CREATE THE TempTable FOR HOLDING TABLE DATA.
	  CREATE TABLE #UserDdata (
	  id INT identity(1,1), Email NVARCHAR(50), Password NVARCHAR(20), userName NVARCHAR(50)
	  )
	  --DECLARE VARIBLE FOR CURSOR.
	  DECLARE @Email NVARCHAR(50)
             ,@Password NVARCHAR(50)
             ,@userName NVARCHAR(50)
      --CREATING CURSOR(cursor_Data) AND Geting DATA FROM tblLogin and Store it to Temptable(#UserDdata). 
	  DECLARE cursor_Data CURSOR
	  FOR 
	  SELECT Email, Password,userName FROM tblLogin;
      OPEN cursor_Data;

	  -- Read data From Cursor.
	  FETCH NEXT FROM cursor_Data INTO 
		@Email, 
		@Password,
		@userName;
      --Looping, If Data Exist.
	  WHILE @@FETCH_STATUS = 0
			BEGIN
			  INSERT INTO #UserDdata(Email,Password,userName) VALUES(@Email,@Password,@userName)
			  -- After Read data From Cursor then FETCH NEXT RECORD from Cursor.
			 FETCH NEXT FROM cursor_Data INTO 
				@Email, 
				@Password,
				@userName;
			END;
        --After Read all Record then It CLOSE THE CURSOR.
		CLOSE cursor_Data;
        DEALLOCATE cursor_Data;
        --Show TempTable Data 
		select*from #UserDdata
   END  

I hope this article will help you to learn, how to create Login and Sign Up page. if you like this article please share Us on