in ,

SQL Server Cursor Instance – Codingvila

Desk of Contents

  • Clarify What’s a cursor in SQL Server?
  • Clarify the Use of the cursors.
  • Clarify the Life Cycle of the cursor.
  • Clarify the cursor With an Instance.

Requirement of Instance

  • Create a Momentary desk for college students.
  • Insert some dummy data into the created desk for demonstrations.
  • Generate/Replace scholar enrollment numbers primarily based on the department, 12 months, and scholar roll no utilizing the cursor.

What’s a cursor in SQL Server?

A Cursor is a SQL Server database object that’s used to govern information in a consequence set on a row-by-row foundation. It acts as a loop similar to the looping mechanism present in some other programming language like C#, VB.Internet, C, C++, Java and and many others. We are able to use cursors once we wish to do information manipulation operations like replace, delete and and many others on a SQL Server database desk in a singleton style in different phrases row by row.

Use of the cursors

You recognize that In relational databases, operations are made on a set of rows known as consequence units. let’s take an instance, In an SQL Server database SELECT assertion returns a set of rows known as a consequence set. Typically the applying logic must work in a singleton style on a brief row-by-row foundation with one row at a time slightly than the complete consequence set directly. This may be performed utilizing cursors in SQL Server.

In any programming language, we use a loop like FOREACH, FOR, WHILE, DO WHILE to iterate by one merchandise at a time, the cursor follows the identical strategy, therefore it is perhaps most popular as a result of it follows the identical logic because the looping mechanism within the programming language.

Life Cycle of the cursor

Right here we’ll break up the life cycle of the cursor into the next 5 completely different sections.

1. Declare Cursor

Earlier than utilizing a cursor, you first should declare the cursor. So, On this part, we’ll declare variables and restore an association of values.

2. Open

That is the second part of the life cycle and As soon as a cursor has been declared, you’ll be able to open it and fetch from it

3. Fetch

That is the third part of the life cycle and that is used to get well the knowledge push by push from a cursor, briefly, you’ll be able to fetch row by row and make a number of operations like insert, replace, delete and and many others on the presently energetic row within the cursor.

4. Shut

That is the fourth part of the life cycle When you might have completed working with a cursor, it is best to shut the cursor. This leaves some portion of the cursor and is used to shut a cursor.

5. Deallocate

That is the fifth and remaining part of the life cycle and on this part, we erase the cursor definition and discharge all of the assets associated to the cursor.

Implementation of Instance

So, Let’s begin to Implement an instance of the cursor within the SQL server, as per our requirement we’ll take into account an instance of a scholar database the place we have to generate the enrollment no of the coed primarily based on his/her department, the 12 months roll variety of the coed.

Earlier than beginning with the cursor I’ll present you the syntax of the cursor in an SQL server and how one can declare a cursor in an SQL server.

Syntex of Cursor

DECLARE @YourVariables  nvarchar(50)  -- Declare All Required Variables
DECLARE MyCursor_Name CURSOR --- Declare The Identify of Your Cursor
 [LOCAL | GLOBAL]--- Outline Scope of Your Cursor
 [FORWARD_ONLY | SCROLL] --Outline Motion Route of Your Cursor
 [ KEYSET | DYNAMIC |STATIC | FAST_FORWARD]--Outline The Fundamental Sort of Your Cursor
 [  SCROLL_LOCKS | OPTIMISTIC |READ_ONLY ]--Outline Locks for Your Cursor
 OPEN MyCursor_Name --Open Your Cursor
 FETCH NEXT FROM MyCursor_Name --Fetch information From Your Cursor
-- Software Logic, Implement SQL QUery Insert, Replace, Delete and many others.
 CLOSE MyCursor_Name --Shut Your Cursor

Now, We’ll begin with our instance for demonstration. So, Let’s create a desk for college students and insert some dummy data within the scholar desk as per our requirement.

Create Desk

DECLARE @College students AS TABLE
      Id INT ,
      RollNo INT ,
      EnrollmentNo NVARCHAR(15) ,
      Identify NVARCHAR(50) ,
      Department NVARCHAR(50) ,
      College NVARCHAR(50)

Insert Data on Tabel

INSERT  INTO @College students
        ( Id, RollNo, EnrollmentNo, Identify, Department, College )
VALUES  ( 1, 1, N'', N'Nikunj Satasiya', N'CE', N'RK College' ),
        ( 2, 2, N'', N'Hiren Dobariya', N'CE', N'RK College' ),
        ( 3, 3, N'', N'Sapna Patel', N'IT', N'RK College' ),
        ( 4, 4, N'', N'Vivek Ghadiya', N'CE', N'RK College' ),
        ( 5, 5, N'', N'Pritesh Dudhat', N'CE', N'RK College' ),
        ( 5, 5, N'', N'Hardik Goriya', N'EC', N'RK College' ),
        ( 6, 6, N'', N'Sneh Patel', N'ME', N'RK College' )

Create/ Declare Cursor

@RollNo INT,
@Department NVARCHAR(50) ,
@12 months AS INT
SET @12 months = RIGHT(YEAR(GETDATE()), 2)
    SELECT  Id ,
            @12 months
    FROM    @College students
OPEN MY_data
FETCH NEXT FROM MY_data INTO @Id, @Department, @RollNo,@12 months
        DECLARE @EnrollmentNo NVARCHAR(15)
                SET @EnrollmentNo = 'SOE' + CAST(@12 months AS VARCHAR(2)) + CAST(@Department AS NVARCHAR(50)) + '000' + CAST(@RollNo AS NVARCHAR(10))
                UPDATE @College students SET EnrollmentNo =  @EnrollmentNo WHERE Id =  @Id
        FETCH NEXT FROM MY_data INTO  @Id, @Department, @RollNo,@12 months

Fetch Data from the Database

SELECT * FROM  @College students


If You analyzed the above instance then first I’ve created a brief desk with the title @College students and inserted some dummy data within the desk for performing information manipulation operations on information. Now in case you retrieve the consequence set utilizing a SELECT assertion then you’ll be able to see there’s within the desk the column with the title « EnrollmentNo » has a clean worth.

Supply hyperlink

What do you think?

Written by admin

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

GIPHY App Key not set. Please check settings

As much as 40% off Candy Personalised Items!

How To Write ChatGPT Prompts To Get The Greatest Outcomes