/SQL/What is cursor in sql server with example

What is cursor in sql server with example

Cursor in sql server

DECLARE @TempTable AS TABLE (Id INT, [Name] VARCHAR(200));

INSERT INTO @TempTable
VALUES (1,'Test Name1'),
(2,'Test Name2'),
(3,'Test Name2')

DECLARE 
    @Id INT, 
    @Name VARCHAR(200);

DECLARE cursor_personInfo CURSOR
FOR SELECT 
        Id, 
        [Name]
    FROM 
       @TempTable;

OPEN cursor_personInfo;

FETCH NEXT FROM cursor_personInfo INTO 
    @Id, 
    @Name;

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT CONVERT(VARCHAR(10),@Id) +' '+ @Name;

        FETCH NEXT FROM cursor_personInfo INTO 
            @Id, 
            @Name;
    END;

CLOSE cursor_personInfo;

DEALLOCATE cursor_personInfo;

/*
Declare – Declares the cursor with a name and the select statement which populates the result set
Open – Opens a cursor and populates the cursor by executing the select statement which is specified while declaring a cursor
Fetch – To retrieve a specific row from the cursor based on the fetch arguments like NEXT, FIRST, LAST, etc
Close – Closes the current result set of SQL Server cursor and can be reopened
Deallocate – Removes cursor reference and releases all the resources associated with a cursor
*/

Source: grepper.com

MSSQL CURSOR

/* DECLARA EL CURSOR PARA EL id de product */
/* Y CARGA EN MEMORIA */
DECLARE ProdId CURSOR FOR SELECT id FROM product
/* DECLARA VARIABLE DONDE SE ALMACENARA */
DECLARE @identificador INT
/* UTILITZA EN MEMORIA*/
OPEN ProdId 
/* AGAFA EL PRIMER VALOR */
FETCH next FROM ProdInfo INTO @identificador
WHILE @@fetch_status = 0
BEGIN
    PRINT @identificador
    FETCH NEXT FROM ProdId INTO @identificador
END
/* TANCA UTILITZACIO */
CLOSE  ProdId 
/* ELIMINA DE MEMORIA */
DEALLOCATE ProdId

Source: bluegraded.i234.me

What is purpose of cursor in sql

A SQL Server cursor is a set of T-SQL logic to loop over a predetermined number of rows one at a time. The purpose for the cursor may be to update one row at a time or perform an administrative process such as SQL Server database backups in a sequential manner.

Source: grepper.com

What is cursor in sql server with example

/*
Declare – Declares the cursor with a name and the select statement which populates the result set
Open – Opens a cursor and populates the cursor by executing the select statement which is specified while declaring a cursor
Fetch – To retrieve a specific row from the cursor based on the fetch arguments like NEXT, FIRST, LAST, etc
Close – Closes the current result set of SQL Server cursor and can be reopened
Deallocate – Removes cursor reference and releases all the resources associated with a cursor
*/

ECLARE @TempTable AS TABLE (Id INT, [Name] VARCHAR(200));

INSERT INTO @TempTable
VALUES (1,'Test Name1'),
(2,'Test Name2'),
(3,'Test Name2')

DECLARE 
    @Id INT, 
    @Name VARCHAR(200);

DECLARE cursor_personInfo CURSOR
FOR SELECT 
        Id, 
        [Name]
    FROM 
       @TempTable;

OPEN cursor_personInfo;

FETCH NEXT FROM cursor_personInfo INTO 
    @Id, 
    @Name;

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT CONVERT(VARCHAR(10),@Id) +' '+ @Name;

        FETCH NEXT FROM cursor_personInfo INTO 
            @Id, 
            @Name;
    END;

CLOSE cursor_personInfo;

DEALLOCATE cursor_personInfo;

Source: sqlservertutorial.net

Attributes of cursor in sql

/*
Declare – Declares the cursor with a name and the select statement which populates the result set
Open – Opens a cursor and populates the cursor by executing the select statement which is specified while declaring a cursor
Fetch – To retrieve a specific row from the cursor based on the fetch arguments like NEXT, FIRST, LAST, etc
Close – Closes the current result set of SQL Server cursor and can be reopened
Deallocate – Removes cursor reference and releases all the resources associated with a cursor
*/

DECLARE @TempTable AS TABLE (Id INT, [Name] VARCHAR(200));

INSERT INTO @TempTable
VALUES (1,'Test Name1'),
(2,'Test Name2'),
(3,'Test Name2')

DECLARE 
    @Id INT, 
    @Name VARCHAR(200);

DECLARE cursor_personInfo CURSOR
FOR SELECT 
        Id, 
        [Name]
    FROM 
       @TempTable;

OPEN cursor_personInfo;

FETCH NEXT FROM cursor_personInfo INTO 
    @Id, 
    @Name;

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT CONVERT(VARCHAR(10),@Id) +' '+ @Name;

        FETCH NEXT FROM cursor_personInfo INTO 
            @Id, 
            @Name;
    END;

CLOSE cursor_personInfo;

DEALLOCATE cursor_personInfo;

Source: grepper.com

SQL Cursor

DECLARE @CONTACTID INT 

--will need to declare at least one variable

--in this case, to store values when iterating through cursor

DECLARE SIMPLE_CURSOR CURSOR FOR 
SELECT ID
FROM CONTACT

OPEN SIMPLE_CURSOR

FETCH NEXT FROM SIMPLE_CURSOR  --Start the cursor
INTO @CONTACTID

WHILE @@FETCH_STATUS = 0  --while there is a loaded record, keep processing
BEGIN

--do whatever you need to do
print ('This is where the magic happens! 
Do whatever you need to do (update/insert/delete/stored proc/etc.')

FETCH NEXT FROM SIMPLE_CURSOR INTO @CONTACTID  --fetch next record
END

CLOSE SIMPLE_CURSOR   --close and deallocate
DEALLOCATE SIMPLE_CURSOR

Source: grepper.com

Similar codes

0 vote317 views1 answer
Best way to bind parameters to SqlCommand C#
SQL
0 vote300 views1 answer
Set column width in sqlplus
SQL
1 vote325 views1 answer
Myql find duplicates
SQL
2 votes317 views1 answer
Mysql application.properties spring boot
SQL
8 votes301 views4 answers
Mysql update one table from another table multiple columns
SQL
0 vote292 views1 answer
Sub blocks in sql
SQL
5 votes296 views3 answers
Sql make column nullable
SQL
0 vote303 views3 answers
Mysql delete if not in another table
SQL
0 vote304 views1 answer
Oracle date summer time
SQL
10 votes301 views2 answers
Sql rtrim
SQL