How to make Cursor in SQL Server

Hello in this chapter I will explain about how to make cursor in stored procedure. Cursor is programming language used to access any existing row separately with other record.Cursor is very suitable to solve a difficult problem. In this article I will demonstrate how to make cursor.
1. Create database
create database dbDatabaseExample

2. Create table
create table tbStudent(
NRP varchar(100) primary key not null, Name varchar(100),Address varchar(100)
)

3. Fill data
insert into tbStudent values('0001','Hendry Apryanto','Griya Persada Street')
insert into tbStudent values('0002','Yohan Andreas','Babakan Jeruk Street')
insert into tbStudent values('0003','Tana El San','Semarang Indah Permai Street')
insert into tbStudent values('0004','Tommy Gunawan','Britania Street')

4. Create Procedure

CREATE PROCEDURE SP_SHOWSTUDENTCURSOR
AS 
BEGIN
 --CURSOR DECLARATION
 DECLARE StudentCSR CURSOR
 FOR SELECT * FROM tbStudent 

 --OPEN CURSOR
 OPEN StudentCSR

 --CURSOR VARIABEL
 Declare @NO varchar(100)
 Declare @Name varchar(100)
 Declare @Address varchar(100)
 
 FETCH NEXT FROM StudentCSR into @NO,@Name,@Address
 While @@FETCH_STATUS=0
 BEGIN
  PRINT '--------------------'
  PRINT 'NO      '+@NO
  PRINT 'NAME    '+@Name
  PRINT 'ADDRESS '+@Address
  PRINT '--------------------'
 FETCH NEXT FROM StudentCSR into @NO,@Name,@Address
 END

 --CLOSE CURSOR
 CLOSE StudentCSR

 --ALLOCATE CURSOR
 DEALLOCATE StudentCSR
END

Note:
Use 'exec [STOREDPROCEDURE_NAME](exec SO_SHOWSTUDENTCURSOR)' if you want to execute.
Result :


Okay that's is it and congratulation you have made Cursor in sql server. I hope this tutorial can help you.Thank you..Have a nice day all..^^

Read More......